Teaching - the subject of self-regulation - disable the selection of children

Question

Do I have a very simple entity (WpmMenu) that contains menu items related to each other regarding self-reference (the called list, which he named)? so in my essence I have:

protected $id protected $parent_id protected $level protected $name 

with all getters / setters relationships:

 /** * @ORM\OneToMany(targetEntity="WpmMenu", mappedBy="parent") */ protected $children; /** * @ORM\ManyToOne(targetEntity="WpmMenu", inversedBy="children", fetch="LAZY") * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onUpdate="CASCADE", onDelete="CASCADE") */ protected $parent; public function __construct() { $this->children = new ArrayCollection(); } 

And everything works fine. When I render the menu tree, I get the root element from the repository, get its children, and then go through each child, get its children and do it recursively until I display each element.

What happens (and why I'm looking for a solution): At the moment I have 5 levels = 1 element, and each of these items has 3 levels = 2 items (and in the future I will use level = 3 elements). To get all the elements of my Doctrine menu tree:

• 1 query for root element +
• 1 request for 5 children (level = 1) of the root element +
• 5 requests for 3 children (level = 2) of each of the objects of level 1 +
• 15 requests (5x3) for children (level = 3) for each level 2 items

TOTAL: 22 requests

So, I need to find a solution for this, and ideally, I would like to have only 1 query.

So here is what I am trying to do: In my object repository (WpmMenuRepository) I use queryBuilder and get a flat array of all menu items sorted by level. Get the root element (WpmMenu) and manually add its children from the loaded array of elements. Then do it recursively on the children. For this, I could have the same tree, but with one query.

So this is what I have:

WpmMenuRepository:

 public function setupTree() { $qb = $this->createQueryBuilder("res"); /** @var Array */ $res = $qb->select("res")->orderBy('res.level', 'DESC')->addOrderBy('res.name','DESC')->getQuery()->getResult(); /** @var WpmMenu */ $treeRoot = array_pop($res); $treeRoot->setupTreeFromFlatCollection($res); return($treeRoot); } 

and in my WpmMenu object I have:

 function setupTreeFromFlatCollection(Array $flattenedDoctrineCollection){ //ADDING IMMEDIATE CHILDREN for ($i=count($flattenedDoctrineCollection)-1 ; $i>=0; $i--) { /** @var WpmMenu */ $docRec = $flattenedDoctrineCollection[$i]; if (($docRec->getLevel()-1) == $this->getLevel()) { if ($docRec->getParentId() == $this->getId()) { $docRec->setParent($this); $this->addChild($docRec); array_splice($flattenedDoctrineCollection, $i, 1); } } } //CALLING CHILDREN RECURSIVELY TO ADD REST foreach ($this->children as &$child) { if ($child->getLevel() > 0) { if (count($flattenedDoctrineCollection) > 0) { $flattenedDoctrineCollection = $child->setupTreeFromFlatCollection($flattenedDoctrineCollection); } else { break; } } } return($flattenedDoctrineCollection); } 

And here is what happens:

Everything works fine, BUT I get two menu items that are present twice .;) Instead of 22 queries, I now have 23. Therefore, I really worsened this business.

What really happens, I think, is that even if I add children added manually, the WpmMenu object is NOT considered in sync with the database, and as soon as I do a foreach loop on my children, loading starts when ORM loads and adding the same children that were already added "manually."

Q Is there a way to block / disable this behavior and talk about these entities that they synchronize with db, so no additional request is required?

Answer 1

With great relief (and a lot of information about the Doctrine of the Doctor and UnitOfWork) I found the answer to this question. And as with so many things, when you find the answer, you realize that you can achieve this with a few lines of code. I am still checking this for unknown side effects, but it seems to work correctly. I had quite a few difficulties to determine what the problem was - once I did this, it was much easier to find the answer.

Thus, the problem is this: since it is a self-regulatory object, where the whole tree is loaded as a flat array of elements, and then they are “fed manually” into the $ children array of each element by the setupTreeFromFlatCollection method - when the getChildren () method is called on any of the entities in the tree (including the root element), Doctrine (not knowing about this “manual” approach) sees the element as “NOT INITIALIZED” and therefore runs SQL to retrieve all the children associated with it from the database.

So, I parsed the ObjectHydrator class (\ Doctrine \ ORM \ Internal \ Hydration \ ObjectHydrator), and I did (sort of) a dehydration process, and I got to $reflFieldValue->setInitialized(true); @line: 369, which is a method on \ Doctrine \ ORM \ PersistentCollection, sets the $ initialized class property to true / false. Therefore, I tried and IT WORKS !!!

Doing a → setInitialized (true) for each of the objects returned by the getResult () method of queryBuilder (using HYDRATE_OBJECT === ObjectHydrator), and then calling → getChildren () on the joints will now NOT trigger any further SQL queries !!!

Integrating it into the WpmMenuRepository code, it becomes:

 public function setupTree() { $qb = $this->createQueryBuilder("res"); /** @var $res Array */ $res = $qb->select("res")->orderBy('res.level', 'DESC')->addOrderBy('res.name','DESC')->getQuery()->getResult(); /** @var $prop ReflectionProperty */ $prop = $this->getClassMetadata()->reflFields["children"]; foreach($res as &$entity) { $prop->getValue($entity)->setInitialized(true);//getValue will return a \Doctrine\ORM\PersistentCollection } /** @var $treeRoot WpmMenu */ $treeRoot = array_pop($res); $treeRoot->setupTreeFromFlatCollection($res); return($treeRoot); } 

And it's all!

Answer 2

Add annotation to your association to provide high load. This should allow you to load the entire tree with just one request and no need to restore it from a flat array.

Example:

 /** * @ManyToMany(targetEntity="User", mappedBy="groups", fetch="EAGER") */ 

Annotations is one, but with a modified value https://doctrine-orm.readthedocs.org/en/latest/tutorials/extra-lazy-associations.html?highlight=fetch

Answer 3

You cannot solve this problem if you use a neighboring list. Been there, done it. The only way is to use a nested set, and then you can get everything you need in one request.

I did this when using Doctrine1. In the nested set, you have the columns root , level , left and right , which you can use to limit / expand the extracted objects. This requires several complex subqueries, but it is doable.

The D1 documentation for the nested set is pretty good, I suggest checking it out and you will understand the idea better.

Answer 4

This is more like a completion and cleaner solution, but based on the accepted answer ...

The only thing needed is a custom repository that will query the structure of the flat tree, and then, iterating over this array, it will first mark the collection of children as initialized, and then humidify it with the setter addChild present in the parent object.

 <?php namespace Domain\Repositories; use Doctrine\ORM\EntityRepository; class PageRepository extends EntityRepository { public function getPageHierachyBySiteId($siteId) { $roots = []; $flatStructure = $this->_em->createQuery('SELECT p FROM Domain\Page p WHERE p.site = :id ORDER BY p.order')->setParameter('id', $siteId)->getResult(); $prop = $this->getClassMetadata()->reflFields['children']; foreach($flatStructure as &$entity) { $prop->getValue($entity)->setInitialized(true); //getValue will return a \Doctrine\ORM\PersistentCollection if ($entity->getParent() != null) { $entity->getParent()->addChild($entity); } else { $roots[] = $entity; } } return $roots; } } 

edit: the getParent () method will not call additional requests until the connection is made with the primary key, in my case the $ parent attribute is a direct relation to the PC, so UnitOfWork will return the cached one and not query the database. If your property is not associated with PK, it will generate additional requests.