Doctrine select either innerJoined entities OR entities having no association

Question

I have the following associative model with users and adresses :

User
 - id
 - email
 - password

Address
 - id
 - street
 - zip_code
 - city
 - user_id

One user has [0:n] Address :

users (id, email, password)

======================================
| 1 | "someone1@example.org" | "..." |
| 2 | "someone2@example.org" | "..." |
| 3 | "someone3@example.org" | "..." |
| 4 | "someone4@example.org" | "..." |
| 5 | "someone5@example.org" | "..." |
======================================

addresses (id, street, zip_code, city, user_id)

===================================================
| 1  | "Somewhere"       | "00001" | "City 1" | 1 |
| 2  | "Somewhere else"  | "00002" | "City 2" | 1 |
| 3  | "Somewhere"       | "00003" | "City 3" | 1 |
| 4  | "Somewhere else"  | "00001" | "City 1" | 2 |
| 5  | "Somewhere"       | "00002" | "City 2" | 2 |
| 6  | "Somewhere else"  | "00003" | "City 3" | 2 |
| 7  | "Somewhere"       | "00001" | "City 1" | 3 |
| 8  | "Somewhere else"  | "00003" | "City 3" | 3 |
| 9  | "Somewhere"       | "00002" | "City 2" | 4 |
| 10 | "Somewhere else"  | "00003" | "City 3" | 4 |
===================================================

I would like to select users having an address in "City 1" : addresses.id IN (1, 4, 7), and for some reason, I need to include users having exactly 0 address as well.

==> [ 1 (address #1), 2 (address #4), 3 (address #7) and 5 (no address) ], but not user 4 (who has addresses but none matching).

Here are a few queries I tried...

1. INNER JOIN

==> [ 1 (address #1), 2 (address #4), 3 (address #7) ] (but not user 5)

$queryBuilder = $this->em->createQueryBuilder('u');

$queryBuilder
    ->innerJoin('u.addresses', 'a', \Doctrine\ORM\Query\Expr\Join::WITH, $queryBuilder->expr()->in('a.id', ':ids'))
    ->setParameter('ids', [1, 4, 7])
;

2. LEFT JOIN

==> [ 1 (address #1, #2, #3), 2 (address #4, #5, #6), 3 (address #7, #8), 4 (address #9, #10), 5 (no address) ]

$queryBuilder = $this->em->createQueryBuilder('u');

$queryBuilder
    ->leftJoin('u.addresses', 'a', \Doctrine\ORM\Query\Expr\Join::WITH, $queryBuilder->expr()->in('a.id', ':ids'))
    ->setParameter('ids', [1, 4, 7])
;

3. LEFT JOIN + addSelect('a')

==> [ 1 (address #1), 2 (address #4), 3 (address #7), 4 (no address), 5 (no address) ]

$queryBuilder = $this->em->createQueryBuilder('u');

$queryBuilder
    ->leftJoin('u.addresses', 'a', \Doctrine\ORM\Query\Expr\Join::WITH, $queryBuilder->expr()->in('a.id', ':ids'))
    ->addSelect('a')
    ->setParameter('ids', [1, 4, 7])
;

4. INNER JOIN + SIZE() condition

==> [ 1 (address #1), 2 (address #4), 3 (address #7) ] (but not user 5)

$queryBuilder = $this->em->createQueryBuilder('u');

$queryBuilder
    ->innerJoin('u.addresses', 'a', \Doctrine\ORM\Query\Expr\Join::WITH, $queryBuilder->expr()->in('a.id', ':ids'))
    ->orWhere($queryBuilder->expr()->eq('SIZE(u.addresses)', 0))
    ->setParameter('ids', [1, 4, 7])
;

Is that possible with a single query, and if it is, how?

Answer 1

If you want to select users that do not have any addresses, you need to perform a LEFT JOIN and test if the retrieved value is NULL (as explained here). Thus, if you wish to combine both conditions, you will need to build your query like this:

$qb = $this->createQueryBuilder('u')
    ->leftJoin('u.addresses', 'a')
    ->addSelect('a') // If you wish to retrieve the address at the same time
    ->where('a.id IS NULL OR a.id IN (:ids)')
    ->setParameter('ids', $ids);

Given your use case, you could even write the where condition like this: 'a.id IS NULL OR a.city = :cityName' to filter by city name and avoid having to retrieve the ids of the addresses entries beforehand.

---

With the above query builder, Doctrine generates an SQL query that looks like this:

SELECT ... FROM users u0_ 
LEFT JOIN addresses a1_ ON u0_.id = a1_.user_id 
WHERE a1_.id IS NULL OR a1_.id IN (1, 4, 7)