Why addSelect() in queryBuilder change my query result

Question

I have a query in entity repository with a ManyToMany other entity : Meeting have many Users

This is my qb :

$userIds = [2];
$qb = $this->createQueryBuilder('m')
    ->leftJoin('m.users', 'mu')
    ->leftJoin('mu.user', 'u')
    ->where('u.id IN (:ids)')
    ->setParameter('id', $userIds);

This work well, if i have a meeting with two users and the user with id=2 is in, the query give the full result with two users.

My problem is that i have many db queries (84) when i display users informations, so i use addSelect like this :

    $userIds = [2];
    $qb = $this->createQueryBuilder('m')
        ->leftJoin('m.users', 'mu')
        ->addSelect('mu')
        ->leftJoin('mu.user', 'u')
        ->addSelect('u')
        ->where('u.id IN (:ids)')
        ->setParameter('id', $userIds);

Great ! i have now only 16 queries for display informations. but my query return only the user with id=2 in my meeting with two users ???

I have the same problem if i keep only the addSelect('mu'). I think the addSelect() is not execute in users who are not my search.

How retrieve the entire results with addSelect() to reduce queries ?

Thanks for help

G1.3 · Answer 1 · 2020-03-26T14:30:35.470

You can add another leftJoin to filter by meeting attended by the user. This leftJoin will not be part of the addSelect, so in the original addSelect, you will have all the users attending to each meeting

You can change the query to:

$qb = $this->createQueryBuilder('m')
    ->leftJoin('m.users', 'mu')
    ->addSelect('mu')
    ->leftJoin('mu.user', 'u')
    ->addSelect('u')
    ->leftJoin('mu.user', 'u_filter')
    ->where('u_filter.id IN (:ids)')
    ->setParameter('id', $userIds)
;

Why addSelect() in queryBuilder change my query result

1 Answers1