Doctrine2 left join on multiple levels making multiple requests

Question

I have 4 entities that are related in hierarchical levels: Company, Department and Employee. Company and Department are related with a ManyToOne bidirectional relation. Department and Employee are related through another entity with 2 OneToMany bidirectional relations because I needed additional parameters for the relation. So basically the final schema is this :

Company <-> Department <-> DepartmentEmployee <-> Employee

I'm trying to select one department from the company of the current user and to get all the employees of this department. I'm using a custom repository to build my query with the query builder like this:

// DepartmentRepository.php

public function getOneWithEmployees($slug, $company)
{
    $qb = $this->createQueryBuilder('d')
            ->where('d.slug = :slug')
            ->andWhere('c.slug = :company')
            ->setParameters(array('slug' => $slug, 'company' => $company))
        ->leftJoin('d.company', 'c')
        ->addSelect('c')
        ->leftJoin('d.departmentEmployee', 'r')
        ->addSelect('r')
        ->leftJoin('r.employee', 'e')
        ->addSelect('e');

    return $qb->getQuery()->getOneOrNullResult();
}

The point being to reduce the number of queries made, but when I execute this query, I still get 32 queries made to the database (I have 15 employees in the department).

When I remove the part

->leftJoin('r.employee', 'e')
->addSelect('e')

I get only one query executed like expected.

How can I do a left join on a left join without triggering multiples queries?

Answer 1

My Employee entity is the inverse side of 2 OneToOne relations: User and Invitation. When I explicitly include these relations in the query with left join, no extra queries are made, but if I leave them out then Doctrine automatically makes queries to fetch them. Looking in the Doctrine FAQ I found this:

4.7.1. Why is an extra SQL query executed every time I fetch an entity with a one-to-one relation?

If Doctrine detects that you are fetching an inverse side one-to-one association it has to execute an additional query to load this object, because it cannot know if there is no such object (setting null) or if it should set a proxy and which id this proxy has.

To solve this problem currently a query has to be executed to find out this information.

Link

So the only solution to avoid extra queries is to build my query like this:

$qb = $this->createQueryBuilder('d')
        ->where('d.slug = :slug')
        ->andWhere('c.slug = :company')
        ->setParameters(array('slug' => $slug, 'company' => $company))
    ->leftJoin('d.company', 'c')
    ->addSelect('c')
    ->leftJoin('d.departmentEmployee', 'r')
    ->addSelect('r')
    ->leftJoin('r.employee', 'e')
    ->addSelect('e')
    ->leftJoin('e.user', 'u')
    ->addSelect('u')
    ->leftJoin('e.invitation', 'i')
    ->addSelect('i');

return $qb->getQuery()->getOneOrNullResult();