mysql - Doctrine 选择 innerJoined 实体或没有关联的实体

Question

我有以下与用户和地址相关联的模型:

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 |
===================================================

我想选择地址在“城市 1”中的用户:addresses.id IN (1, 4, 7)，出于某种原因，我还需要包括地址正好为 0 的用户。

==> [ 1(地址#1)、2(地址#4)、3(地址#7)和 5(无地址)]，但不是用户 4(有地址但不匹配)。

以下是我尝试过的一些查询...

1. 内连接

==> [ 1(地址#1)，2(地址#4)，3(地址#7)](但不是用户 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])
;

1. 左连接

==> [ 1(地址#1、#2、#3)、2(地址#4、#5、#6)、3(地址#7、#8)、4(地址#9、# 10), 5(无地址)]

$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])
;

1. LEFT JOIN + addSelect('a')

==> [ 1(地址#1)，2(地址#4)，3(地址#7)，4(无地址)，5(无地址)]

$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])
;

1. INNER JOIN + SIZE() 条件

==> [ 1(地址#1)，2(地址#4)，3(地址#7)](但不是用户 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])
;

这是否可以通过单个查询实现，如果可以，如何实现？

Answer 1

如果您想选择没有任何地址的用户，您需要执行 LEFT JOIN 并测试检索到的值是否为 NULL(如 here 所述) .因此，如果您希望结合这两个条件，则需要像这样构建查询:

$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);

鉴于您的用例，您甚至可以像这样编写 where 条件:'a.id IS NULL OR a.city = :cityName' 按城市名称过滤并避免检索预先输入 addresses 条目的 ID。

---

使用上面的查询构建器，Doctrine 生成一个 SQL 查询，如下所示:

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)