Most efficient way to batch load entities and all of their child entities in Doctrine2

doctrine, doctrine-orm, symfony

Solution

This is trivially easy in Doctrine (as you would expect) but my search of the documentation didn't bring up a clear explanation of how to do this. There is however a fantastic talk by Guilherme Blanco that explains how to do this. (look from slide 22 onwards)

I note that the example you provided used DQL but it is also possible to do this with the query builder.

In DQL:

SELECT p, c
FROM Parent p
LEFT JOIN p.children c

Using the `QueryBuilder`

$qb->select(['p', 'c'])
   ->from('Parent', 'p')
   ->leftJoin('p.children', 'c')
;

Running either of the above queries will hit the database just once and return an array of Parent objects with all of their children fully hydrated. If you need to join more entities you can do so but just remember if you want doctrine to actually hyrdrate those objects for you then you must add the joined entity to the select statement. For example:

$qb->select(['p', 'c', 'gc'])
   ->from('Parent', 'p')
   ->leftJoin('p.children', 'c')
   ->leftJoin('c.grandchildren', 'gc')
;

Problem

I have one entity type I'll call parent with a one-to-many relationship with an entity I'll call child (many children belong to a single parent). What I'd like to do is load--as efficiently as possible--the parent entity and all of its children with as few queries as possible. By default, with doctrine, if I do something like load 100 of my Parent entity, then loop through them and do operations on the Children, it will be at least 101 queries, one to load the Parent and one to get all children for each parent. If I try to write a query that loads all the objects in one pass it gets incredibly slow, loading what I can only assume is a cartesian object with an entire row of all Parent and Child properties for each child. This problem gets even more devastating if I have multiple child entities, which I do. The only solution I can think of involves querying all parents and all children independently, then associating them in a nested loop. This cuts it down to only two queries, but seems...not right. Anyone have any insight for me? Here's essentially what I'm thinking: ``` //First, select an array of all parent objects $parents = "SELECT p FROM parent p WHERE 1 LIMIT 100"; //Select all child elements $children = "SELECT c FROM children c WHERE c.parent IN ($parents)"; //Loop through all elements and assign them to parents, and parents to children foreach($children as $child){ foreach($parents as $parent){ //All children will have loaded references to their parent object if ($child->getParent()->getId() === $parent->getId()){ //This child belongs to this parent $parent->addChild($child); $child->setParent($parent); continue; } } } ``` Edit 1: In response to Tom Corrigan's comment below: Using the query builder just like your suggestion: ``` $qb->select(['p', 'c']) ->from('Parent', 'p') ->leftJoin('p.children', 'c') ; ``` I do indeed load the entire object, but the performance hit is ridiculous. Using the above query to load two parent entities with a total of about 12 child entities took almost 40 seconds. If I load the parent entity THEN load the two child entities it takes about 5ms. Edit 2: OK the issue with the incredibly long load time only occurs when I'm querying based upon a joined many-to-many table. If I do a simple select like you described above it works quite fast, so thank you Tom you answered my question perfectly. What I don't understand---and this is a different question entirely but I'll mention it here because it is related on some level---is why, when I do multiple selects, it takes an INCREDIBLY long time if I filter by one of the selected sub columns. This example adds two more entities to the mix, groups (many to many with people) and application (one to many with groups). ``` $qb->select(['p', 'c', 'g', 'a']) ->from('Parent', 'p') ->leftJoin('p.children', 'c') ->join('p.groups','g') ->join('g.application','a') ->where('a.id = :applicationId') ->addGroupBy('p') ->setMaxResults(1) ; ``` This query will take nearly a minute to process, whereas, if I do the same thing without the extra selects like so: ``` $qb->select('p') ->from('Parent', 'p') ->leftJoin('p.children', 'c') ->join('p.groups','g') ->join('g.application','a') ->where('a.id = :applicationId') ->setMaxResults(1) ; ``` It takes a few MS, and adding the additional entities a few ms more. Any thoughts?

Original source