Doctrine - self-referencing entity - disable fetching of children

Question

I have a very simple entity(WpmMenu) that holds menu items connected to one another in a self-referencing relationship (adjecent list it's called)? so in my entity I have:

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

with all the getters/setters the relationships are:

/**
* @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 loop through each child, get its children and do this recursively until I have rendered each item.

What happens (and for what I am seeking a solution)is this: At the moment I have 5 level=1 items and each of these items have 3 level=2 items attached (and in the future I will be using level=3 items as well). To get all elements of my menu tree Doctrine executes:

• 1 query for the root element +
• 1 query to get the 5 children(level=1) of the root element +
• 5 queries to get the 3 children(level=2) of each of the level 1 items +
• 15 queries (5x3) to get the children(level=3) of each level 2 items

TOTAL: 22 queries

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

So this is what I am trying to do: In my entities repository(WpmMenuRepository) I use queryBuilder and get a flat array of all menu items ordered by level. Get the root element(WpmMenu) and add "manually" its children from the loaded array of elements. Then do this recursively on children. Doing this way I could have the same tree but with a single 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 entity 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 this is what happens:

Everything works out fine, BUT I end up with each menu items present twice. ;) Instead of 22 queries now I have 23. So I actually worsened the case.

What really happens, I think, is that even if I add the children added "manually", the WpmMenu entity is NOT considered in-sync with the database and as soon as I do the foreach loop on its children the loading is triggered in ORM loading and adding the same children that were added already "manually".

Q: Is there a way to block/disable this behaviour and tell these entities they they ARE in sync with the db so no additional querying is needed?

Rephrase

See if this helps: docs.doctrine-project.org/en/latest/reference/… – Grandmotherly

Nope. I do not have partial objects and it seems quite a bad idea to go that way. – Rephrase

Answer 1

With immense relief (and a lots of learning about Doctrine Hydration and UnitOfWork) I found the answer to this question. And as with lots of things once you find the answer you realize that you can achieve this with a few lines of code. I am still testing this for unknown side-effects but it seems to be working correctly. I had quite a lot of difficulties to identify what the problem was - once I did it was much easier to search for an answer.

So the problem is this: Since this is a self-referencing entity where the entire tree is loaded as a flat array of elements and then they are "fed manually" to 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 so executes an SQL to fetch all its related children from the database.

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

Doing a ->setInitialized(true) on each of the entities returned by the getResult() method of the queryBuilder (using the HYDRATE_OBJECT === ObjectHydrator) and then calling ->getChildren() on the entities now do NOT trigger any further SQLs!!!

Integrating it in the code of WpmMenuRepository, 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 that's all!

Rephrase

I can firmly say that no one can find this elaborate solution any where else on the web. not even in doctrine documentations you can find a mention to this common situation which happens alot for example when rendering nested menues or comments. thank you @Rephrase you made my day. – Nikaniki

I am facing the exact same problem. The problem radicates here: The identity map being indexed by primary keys only allows shortcuts when you ask for objects by primary key. This means, that when you call getChildren() all this pages queries the UnitOfWork by a non-primary key field (aka $parent) and then doctrine queries the database again... What you are doing is basically preventing further Lazy Loading and initializing the children PersistentCollection manually... as you are only setting $initialized on the children PersistentCollection I don't see any drawbacks.. – Exceed

This is indeed a hacky solution.. But far best than all the mess that nested-sets – Exceed

Oh, and btw, you can iterate the flat array and only use the addChild method for hydrating the childrens array.. that way you don't need recursive calls and the "level" attribute – Exceed

Sending you some brolove! Thanks man, my code is MUCH faster now! – Bandoline

Thank you very much, @jakabadambalazs. It helped me on the latest project I was involved in. Hopefully, you don't mind that I've posted a detailed article based off of your answer. – Inadvertent

Answer 2

Add the annotation to your association to enable eager loading. This should allow you to load the entire tree with only 1 query, and avoid having to reconstruct it from a flat array.

Example:

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

The annotation is this one but with the value changed https://doctrine-orm.readthedocs.org/en/latest/tutorials/extra-lazy-associations.html?highlight=fetch

Sinkhole

No cigar! The fetch="EAGER" option on the relationship triggers the loading of the entire tree correctly as soon as I fetch the root element of the tree. However to do that all the other queries get executed with WHERE t0.parent_id = ? as before. In other words, the only change is that the queries get executed even if I do not access the children but the actual structure and hence the queries needed to fetch the elements is the same - so the result is the same – Rephrase

Answer 3

You can't solve this problem if using adjacent list. Been there, done that. The only way is to use nested-set and then you would be able to fetch everything you need in one single query.

I did that when I was using Doctrine1. In nested-set you have root, level, left and right columns which you can use to limit/expand fetched objects. It does require somewhat complex subqueries but it is doable.

D1 documentation for nested-set is pretty good, I suggest to check it and you will understand the idea better.

Ethelda

Thanks, but still at dark! This is what I got to: 1) nested sets are really cool - I spent 4hrs to read up on it and i learned a great deal but at the end of the day at one point I will be doing a query with QueryBuilder on which I can: ->getResult() / ->getArrayResult() / ->getScalarResult() and neither of these methods will return what I need. The ->getArrayResult() is the closest 'cos it returns a nested array of arrays (at least this is what it seems to be doing but I still need to check into it) - but I think the actual problem is not how i lay out my data in db but the hydration – Rephrase

In fact, as it stands in the (doctrine-orm.readthedocs.org/en/latest/reference/…) doctrine reference book @14.7.4 - Hydration modes - there seems to be nothing that will hydrate flat 2D data to tree-like structure on a single result set. No matter if adjacent list or nested set. – Rephrase

Let me check if I understood you correctly; you want to have one query that will fetch entire tree, maybe even with some joins, right? If that is so, you really have to use nested-set. I can copy&paste the sample code I have but it is for D1 and very specific for project so it wouldn't be of much use for you. The trick was in clever use of left and right columns. The easiest solution would be to just create another entity that is nested-set, create some tree and check values in DB. Then you will get an idea how to do this. And btw; I hydrated to objects, no conversion were required. – Ethelda

Yes, you got me right - I want ONE single query fetching the entire tree and I want it to be hydrated to a "tree" of entities where having the reference to the root element I can get to any child entity (by ->getChildren() or so) without additional queries executed. For me, (maybe I am completely wrong on this), the difference between adjacent list and nested-set is that with a nested-set i can load a specific branch of the tree while with adj.list I cannot - but since i need the entire tree I see no difference cos i want all records with all lft and rgt values – Rephrase

...and one more thing, the code above in my question is quite a stupid code but in Symfony1 (which I think was using Doctrine 1.something) it used to work correctly. That is: loading a flat array of entities and feeding them through their addChild() methods used to work. As it does now. The only difference is that with D2 now the UnitOfWork(uow) identifies the manually fed elements as NEW (as opposed to LOADED) and therefore it loads the same elements again. In fact, I bet if i try to persist the root element it will try to insert the 'new' elements in the db. – Rephrase

...NO IT DOESN'T!!! there is something seriously wrong here: $AclAcoList = $AclAcoRoot->getChildren(); foreach($AclAcoList as $AclAcoChild) {echo'<br />Element:' . spl_object_hash($AclAcoChild);} ...gives me: Element:0000000077f1a577000000001d5117a8 Element:0000000077f1a54f000000001d5117a8 Element:0000000077f1a57f000000001d5117a8 Element:0000000077f1a57c000000001d5117a8 Element:0000000077f1a577000000001d5117a8 Element:0000000077f1a57c000000001d5117a8 Element:0000000077f1a54f000000001d5117a8 Element:0000000077f1a57f000000001d5117a8 - Try to copy and search any of them hashes in this page!2X! – Rephrase

Answer 4

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

The only thing needed is a custom repository that is going to query the flat tree structure, and then, by iterating this array it will, first mark the children collection as initialized and then will hydratate it with the addChild setter present in the parent entity..

<?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 trigger additional queries as long as the relationship is made to the primary key, in my case, the $parent attribute is a direct relationship to the PK, so the UnitOfWork will return the cached entity and not query the database.. If your property doesn't relates by the PK, it WILL generate additional queries.

Exceed