How to write this doctrine query with multiple joins?

Question

I have an entity Person (p) which is related to an entity Notifications related to an entity NotificationType.

Some persons have received a notification of type 1 (notification_type_id = 1).
Some have received notifications but not of the type 1
some haven't received messages at all.

Globally, I want to retrieve those who haven't received the messages of type 1.

I have written this query :

$qb = $this->createQueryBuilder('p')
        ->leftJoin('p.notifications', 'n')
        ->leftJoin('p.notification_type', 'nt')
        ->addSelect(array('p','n','nt'))
        ->where('nt.id NOT IN (1)')
        ->orderBy('p.name', 'ASC');
return $qb->getQuery()->getResult();

But with this query I only get those who have received notifications but not of type 1, I don't get those who haven't received notifications at all.

How can I rectify my query to have those ones ?

Thanks a lot for your help

score 0 · Answer 1 · answered Dec 19 '12 at 18:03

NOT IN with null values (for your joins where there are no messages) will not parse as you expect. Similarly for eq or ne.

Try:

$qb = $this->createQueryBuilder('p');
$results = $qb->leftJoin('p.notifications', 'n')
              ->leftJoin('p.notification_type', 'nt')
              ->addSelect(array('p','n','nt'))
              ->where('nt.id !=1')
              ->orWhere('nt.id IS NULL')
              ->orderBy('p.name', 'ASC');
return $qb->getQuery()->getResult();

You can also put these as conditions on your join, refer to 13.2.5. The Expr class of the doctrine code

As an aside, you probably don't need the select since you can access those values programmatically via getters in the entity.

How to write this doctrine query with multiple joins?

1 Answers1