Doctrine 2中实现FROM子句嵌套子查询筛选负收益项目

我尝试自行解决问题但未找到可行方案，现求助：
有两个关联Project::class的实体，对应收入和成本：

• Invoice::amount()（收入）
• SupplierInvoice::amount()（成本）

需求是筛选出所有收益为负的Project实体，并计算收益值，需要实现两类查询：

1. 分页查询Project实体（每页20条），展示每条记录的收益；
2. 计算所有符合条件的Project实体的总收益并展示（例如：总收益：-10000美元）。

我已有可正常运行的原生SQL，但无法在Doctrine 2中实现逻辑。重要提示：主查询需控制Project的查询范围，因为存在8种不同场景的额外筛选条件，子查询必须嵌入主查询中。

环境信息 #

• PHP版本：8.1
• MySQL版本：8.0
• Doctrine版本：doctrine/orm": "^2.6，doctrine/migrations": "^2.3

可正常运行的原生SQL #

SELECT 
    SUM(
        (
            SELECT SUM(i.amount) 
            FROM invoice i
            WHERE i.project_id = p.id
        )
    ) AS income,
    SUM(
        (
            SELECT SUM(si.amount) 
            FROM supplier_invoice si
            WHERE si.project_id = p.id
        )
    ) AS cost

FROM project p

WHERE p.id IN (
    SELECT p20.id FROM (
        SELECT
            p10.id,
            SUM(
                (
                    SELECT SUM(i.amount) 
                    FROM invoice i
                    WHERE i.project_id = p10.id
                )
            ) AS inner_income,
            SUM(
                (
                    SELECT SUM(si.amount) 
                    FROM supplier_invoice si
                    WHERE si.project_id = p10.id
                )
            ) AS inner_cost
        FROM project p10
        
        GROUP BY p10.id
        HAVING inner_cost > inner_income
    ) p20
);

尝试的代码及错误 #

我编写了如下测试代码，但运行失败：

public function queryBuilderForEconomyNegativeWhatever(?callable $build_callback = null): callable {
    return function(QueryBuilder $qb) use ($build_callback): void {
        $expr = $this->entity_manager->getExpressionBuilder();
        $qb->andWhere(
            $expr->in('pi.id', sprintf('(
                SELECT p20.id FROM (
                    SELECT
                        p10.id
                    FROM %s p10
                    WHERE p10.id > 500
                ) p20
            )', Project::class))
        );
        if ($build_callback !== null) {
            $build_callback($qb);
        }
    };
}

错误信息：

Doctrine \ ORM \ Query \ QueryException
[Semantical Error] line 0, col 1928 near 'FROM ( SELECT p10.id FROM': Error: Subquery is not supported here

尝试自定义Doctrine函数 #

我还尝试通过自定义Doctrine 2函数解决问题，代码如下：

<?php
declare(strict_types=1);

namespace KaratenBygg\App\Doctrine\Extensions;

use Doctrine\ORM\Query\AST\ArithmeticExpression;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Subselect;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class InSubQuery extends FunctionNode {

    public const NAME = 'insubquery';

    private Subselect $sub_select1;
    private ArithmeticExpression $expr1;
    private ArithmeticExpression $expr2;

    public function parse(Parser $parser): void {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->expr1 = $parser->ArithmeticExpression();
        $parser->match(Lexer::T_COMMA);

        $this->expr2 = $parser->ArithmeticExpression();
        $parser->match(Lexer::T_COMMA);

        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->sub_select1 = $parser->Subselect();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string {
        // pi.id, p20.id, ( SELECT p10.id FROM project p10 )
        return sprintf('%s IN (SELECT %s FROM (%s))',
            $this->expr1->dispatch($sqlWalker),
            $this->expr2->dispatch($sqlWalker),
            $this->sub_select1->dispatch($sqlWalker),
        );
    }
}

Doctrine ORM 2.x不支持在IN子句中直接嵌套两层子查询（即IN (SELECT ... FROM (SELECT ...))形式），可通过以下三种方式解决：

方法一：重构子查询，避免双层嵌套 #

利用Doctrine关联映射，将内层筛选逻辑合并为单层级子查询，直接返回符合条件的Project ID：

public function queryBuilderForNegativeProfitProjects(?callable $buildCallback = null): callable
{
    return function(QueryBuilder $qb) use ($buildCallback): void {
        $em = $this->entity_manager;
        
        // 子查询：计算每个Project的收入、成本，筛选收益为负的ID
        $subQb = $em->createQueryBuilder()
            ->select('p10.id')
            ->from(Project::class, 'p10')
            ->addSelect('COALESCE(SUM(i.amount), 0) AS inner_income')
            ->addSelect('COALESCE(SUM(si.amount), 0) AS inner_cost')
            ->leftJoin('p10.invoices', 'i') // 假设Project实体已定义与Invoice的一对多关联
            ->leftJoin('p10.supplierInvoices', 'si') // 假设Project实体已定义与SupplierInvoice的一对多关联
            ->groupBy('p10.id')
            ->having('inner_cost > inner_income');

        // 主查询筛选ID在子查询结果中的Project
        $qb->andWhere($qb->expr()->in('p.id', $subQb->getDQL()));
        
        // 追加额外筛选条件
        if ($buildCallback !== null) {
            $buildCallback($qb);
        }
    };
}

说明 #

1. 通过leftJoin直接关联实体，替代原生SQL中的嵌套子查询；
2. 使用COALESCE处理无收入/成本的场景，避免计算结果为NULL；
3. 子查询返回符合条件的Project ID，Doctrine支持这种单层级子查询作为IN的参数。

方法二：使用原生SQL查询（复杂场景首选） #

如果关联映射无法满足需求，可直接复用原生SQL，通过Doctrine的原生查询功能实现：

分页查询示例 #

public function getNegativeProfitProjectsPaginated(int $page = 1, int $perPage = 20): array
{
    $offset = ($page - 1) * $perPage;
    $sql = <<<SQL
        SELECT 
            p.*,
            COALESCE((SELECT SUM(i.amount) FROM invoice i WHERE i.project_id = p.id), 0) AS income,
            COALESCE((SELECT SUM(si.amount) FROM supplier_invoice si WHERE si.project_id = p.id), 0) AS cost,
            (COALESCE((SELECT SUM(i.amount) FROM invoice i WHERE i.project_id = p.id), 0) - COALESCE((SELECT SUM(si.amount) FROM supplier_invoice si WHERE si.project_id = p.id), 0)) AS profit
        FROM project p
        WHERE p.id IN (
            SELECT p10.id
            FROM project p10
            LEFT JOIN invoice i ON i.project_id = p10.id
            LEFT JOIN supplier_invoice si ON si.project_id = p10.id
            GROUP BY p10.id
            HAVING COALESCE(SUM(si.amount), 0) > COALESCE(SUM(i.amount), 0)
        )
        LIMIT :perPage OFFSET :offset
    SQL;

    $stmt = $this->entity_manager->getConnection()->prepare($sql);
    $stmt->bindValue('perPage', $perPage, \PDO::PARAM_INT);
    $stmt->bindValue('offset', $offset, \PDO::PARAM_INT);
    $stmt->execute();
    
    return $stmt->fetchAllAssociative();
}

总收益计算示例 #

public function getTotalNegativeProfit(): float
{
    $sql = <<<SQL
        SELECT 
            SUM(
                COALESCE((SELECT SUM(i.amount) FROM invoice i WHERE i.project_id = p.id), 0) - 
                COALESCE((SELECT SUM(si.amount) FROM supplier_invoice si WHERE si.project_id = p.id), 0)
            ) AS total_profit
        FROM project p
        WHERE p.id IN (
            SELECT p10.id
            FROM project p10
            LEFT JOIN invoice i ON i.project_id = p10.id
            LEFT JOIN supplier_invoice si ON si.project_id = p10.id
            GROUP BY p10.id
            HAVING COALESCE(SUM(si.amount), 0) > COALESCE(SUM(i.amount), 0)
        )
    SQL;

    $stmt = $this->entity_manager->getConnection()->prepare($sql);
    $stmt->execute();
    
    $result = $stmt->fetchOne();
    return (float)$result;
}

说明 #

• 原生SQL可完全复用原有逻辑，额外筛选条件直接追加到主查询WHERE子句即可；
• 用COALESCE处理空值，确保计算结果准确；
• 分页通过LIMIT和OFFSET实现，总收益直接通过SUM汇总符合条件的Project收益。

方法三：修复自定义Doctrine函数（可选） #

若坚持使用自定义函数，需修正解析逻辑，让Doctrine正确识别语法：

<?php
declare(strict_types=1);

namespace KaratenBygg\App\Doctrine\Extensions;

use Doctrine\ORM\Query\AST\Node;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Subselect;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class InSubQuery extends FunctionNode {

    public const NAME = 'INSUBQUERY';

    private Node $expression;
    private Subselect $subselect;

    public function parse(Parser $parser): void {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        
        // 解析要匹配的表达式（如p.id）
        $this->expression = $parser->ArithmeticExpression();
        $parser->match(Lexer::T_COMMA);
        
        // 解析双层子查询
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->subselect = $parser->Subselect();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string {
        return sprintf('%s IN (%s)',
            $this->expression->dispatch($sqlWalker),
            $this->subselect->dispatch($sqlWalker)
        );
    }
}

注册自定义函数 #

在Doctrine配置中添加：

# config/packages/doctrine.yaml
doctrine:
    orm:
        entity_managers:
            default:
                dql:
                    numeric_functions:
                        INSUBQUERY: KaratenBygg\App\Doctrine\Extensions\InSubQuery

使用示例 #

$qb->andWhere('INSUBQUERY(p.id, (SELECT p20.id FROM (SELECT p10.id FROM Project p10 WHERE p10.id > 500) p20))');

说明 #

• 简化参数结构，仅需传入匹配表达式和完整的双层子查询；
• DQL中需使用实体类名（如Project），Doctrine会自动映射到对应表名。

内容的提问来源于stack exchange，提问作者Dennis Fransén