如何编写Doctrine DQL查询：获取最后状态非APPROVED及未关联的产品

最终可用的DQL查询 #

SELECT p
FROM App\Entity\Product p
LEFT JOIN App\Entity\ListingRequestValidationStatus ls WITH ls.product = p
LEFT JOIN (
    SELECT ls_inner.product, MAX(ls_inner.id) AS latest_id
    FROM App\Entity\ListingRequestValidationStatus ls_inner
    GROUP BY ls_inner.product
) latest_ls ON latest_ls.product = p.id AND ls.id = latest_ls.latest_id
WHERE ls.status != 'APPROVED' OR ls.id IS NULL
GROUP BY p.id

思路拆解 #

1. LEFT JOIN 保留无验证记录的产品：用LEFT JOIN关联产品和它的验证状态，确保从未提交过验证请求的产品不会被过滤掉
2. 子查询锁定最新状态：一个产品可能有多条验证记录，子查询按产品分组，取每条分组里最大的id（id是自增主键，最大id对应最新的状态），保证我们只判断最后一次的状态
3. 筛选条件命中目标产品：
   • ls.id IS NULL：匹配没有任何验证记录的产品
   • ls.status != 'APPROVED'：匹配有验证记录，但最后一次状态不是APPROVED的产品
4. GROUP BY 去重：避免单个产品因多条旧验证记录被重复返回

仓库层代码示例（ProductRepository） #

public function findProductsNeedingAction(): array
{
    $dql = <<<DQL
        SELECT p
        FROM App\Entity\Product p
        LEFT JOIN App\Entity\ListingRequestValidationStatus ls WITH ls.product = p
        LEFT JOIN (
            SELECT ls_inner.product, MAX(ls_inner.id) AS latest_id
            FROM App\Entity\ListingRequestValidationStatus ls_inner
            GROUP BY ls_inner.product
        ) latest_ls ON latest_ls.product = p.id AND ls.id = latest_ls.latest_id
        WHERE ls.status != :approvedStatus OR ls.id IS NULL
        GROUP BY p.id
    DQL;

    return $this->getEntityManager()
        ->createQuery($dql)
        ->setParameter('approvedStatus', ListingRequestValidationStatus::STATUS['APPROVED'])
        ->getResult();
}

小提示 #

如果你的ListingRequestValidationStatus实体有createdAt时间字段，子查询里用MAX(ls_inner.createdAt)替代MAX(ls_inner.id)会更严谨——毕竟主键自增逻辑可能变动，但创建时间的先后肯定能代表状态的新旧。

内容的提问来源于stack exchange，提问作者khalid sookia