Reduce doctrine queries on hydration of related entities

Suppose you have a schema like the one in the next figure

and in your repository class you fetch an MqProject entity and later in the code you traverse through all MqProject’s related MqJobs and each job’s related MqTasks like

  foreach($project->getJobs as $job) {
    foreach($job->getTasks as $task) {
      printf("job: %s task %s\n", $job->getId(), $task->getId());
    }
  }

Doctrine will execute a query to hydrate each job and a query to hydrate each task entity (lazy hydration). Doctrine may execute 10s of queries. Thats bad.

The solution is to help doctrine with joins to hydrate all related entities at once.

class MqProcessRepository extends EntityRepository {
    
    public function getActiveProcessByProject($project) {
        $out = $this->createQueryBuilder('p')
                ->leftJoin('p.jobs', 'j')
                ->leftJoin('j.tasks', 't')
                ->addSelect('j')
                ->addSelect('t')
                ->andWhere("p.project_id=:prj and p.active=:ac")
                ->getQuery()
                ->setParameter('prj', $project->getId())
                ->setParameter('ac', 1)
                ->getOneOrNullResult();
        return $out;
    }
}

The trick is done with addSelects. We select also the related entities. This way doctrine will hydrate all entities with one query. Beware that if you have lots of related entities a memory issue might come up. But in my case having around 100 entities there is no problem.