如何在Doctrine中通过子查询实现关联统计查询？

问题背景 #

现有User和Business两个Doctrine实体，二者为一对多关联关系（一个用户对应多个商家），需要实现等效于以下SQL的查询：

select
    user.*,
    business_count.count
from
    user
join (
    select
        count(*) as count,
        user_id
    from
        business
    group by
        user_id) as business_count on business_count.user_id = user.id;

原代码尝试在JOIN子句中直接嵌套子查询，执行命令php bin/console test:query时触发错误：

[Semantical Error] line 0, col 48 near 'JOIN (SELECT': Error: Subquery is not supported here

解决方案 #

Doctrine ORM的DQL不支持在JOIN子句中直接使用子查询作为关联表，但可以通过以下几种方式实现需求：

方法1：将子查询作为派生表放入FROM子句 #

利用Doctrine支持的派生表特性（需2.6+版本），将统计子查询作为主查询的数据源之一，通过WHERE条件关联主表：

protected function execute(InputInterface $input, OutputInterface $output): int
{
    // 构建商家统计子查询
    $subDql = $this->em->createQueryBuilder()
        ->select('COUNT(b.id) AS count, b.user_id')
        ->from(Business::class, 'b')
        ->groupBy('b.user_id')
        ->getDql();

    // 主查询关联派生表
    $qb = $this->em->createQueryBuilder()
        ->select('u, bc.count')
        ->from(User::class, 'u')
        ->from("($subDql)", 'bc')
        ->where('u.id = bc.user_id')
        ->setMaxResults(5);

    $query = $qb->getQuery();
    $output->writeln($query->getSql());

    $results = $query->getResult();
    foreach ($results as $result) {
        /** @var User $user */
        $user = $result[0];
        $count = $result['count'];
        $output->writeln("User ID: " . $user->getId());
        $output->writeln("Business Count: " . $count);
        $output->writeln("----------------------------");
    }

    return Command::SUCCESS;
}

方法2：使用JOIN + GROUP BY统计（推荐方案） #

直接利用实体间的关联关系进行JOIN并分组统计，这是Doctrine中最简洁高效的实现方式：

protected function execute(InputInterface $input, OutputInterface $output): int
{
    $qb = $this->em->createQueryBuilder()
        ->select('u, COUNT(b.id) AS businessCount')
        ->from(User::class, 'u')
        ->leftJoin('u.businesses', 'b') // 需确保User实体中关联Business的属性名为businesses（对应@OneToMany映射）
        ->groupBy('u.id')
        ->setMaxResults(5);

    $query = $qb->getQuery();
    $output->writeln($query->getSql());

    $results = $query->getResult();
    foreach ($results as $result) {
        /** @var User $user */
        $user = $result[0];
        $count = $result['businessCount'];
        $output->writeln("User ID: " . $user->getId());
        $output->writeln("Business Count: " . $count);
        $output->writeln("----------------------------");
    }

    return Command::SUCCESS;
}

• 如果仅需要显示有商家关联的用户，将leftJoin替换为join即可
• 该方案性能优于子查询，更符合ORM的设计思路

方法3：在SELECT子句中嵌入子查询 #

若必须使用子查询，可直接在SELECT语句中嵌入统计逻辑：

protected function execute(InputInterface $input, OutputInterface $output): int
{
    $qb = $this->em->createQueryBuilder()
        ->select('u, (SELECT COUNT(b.id) FROM App\Entity\Business b WHERE b.user = u) AS businessCount')
        ->from(User::class, 'u')
        ->setMaxResults(5);

    $query = $qb->getQuery();
    $output->writeln($query->getSql());

    $results = $query->getResult();
    foreach ($results as $result) {
        /** @var User $user */
        $user = $result[0];
        $count = $result['businessCount'];
        $output->writeln("User ID: " . $user->getId());
        $output->writeln("Business Count: " . $count);
        $output->writeln("----------------------------");
    }

    return Command::SUCCESS;
}

此方案适合单条记录的统计，但数据量较大时性能不如JOIN+GROUP BY。

错误原因说明 #

Doctrine DQL存在语法限制：无法在JOIN子句中直接使用子查询作为关联表，子查询仅允许放在FROM（作为派生表）、SELECT或WHERE子句中。

内容的提问来源于stack exchange，提问作者Dimitrios Desyllas