致命错误：达到'100'的最大函数嵌套级别，中止

Question

我有这样的实体：

Invoice hasMany PlannedCharge
PlannedCharge hasMany PartialCharge

我需要知道发票是否已全额支付，下面的sql:也是如此

SELECT 
    Invoice.id
FROM
    webfactory_sale_invoices Invoice
    AND
        0 <> 
        ((SELECT 
            SUM(PlannedCharge.ammount)
        FROM
            webfactory_sale_planned_charges PlannedCharge
        WHERE
            PlannedCharge.invoice_id = Invoice.id
        GROUP BY Invoice.id) - IFNULL((SELECT 
            SUM(PartialCharge.ammount)
        FROM
            webfactory_sale_planned_charges PlannedCharge
                INNER JOIN
            webfactory_sale_partial_charges PartialCharge ON PlannedCharge.id = PartialCharge.planned_charge_id
        WHERE
            PlannedCharge.invoice_id = Invoice.id
        GROUP BY Invoice.id), 0))

现在，使用以下方式通过querybuilder转换为DQL：

$qb->andWhere($qb->expr()->neq(0, $qb->expr()->diff(
    $qb->select('SUM(PlannedCharge.ammount)')
            ->from('WebFactorySaleBundle:PlannedCharge', 'PlannedCharge')
            ->where("PlannedCharge.invoice = {$invoiceAlias}")
            ->groupBy($invoiceAlias)
            , 
    $qb->select('SUM(PartialCharge.ammount)')
            ->from('WebFactorySaleBundle:PlannedCharge', 'PlannedCharge')
            ->innerJoin('WebFactorySaleBundle:PartialCharge', 'PartialCharge')
            ->where("PlannedCharge.invoice = {$invoiceAlias}")
            ->groupBy($invoiceAlias)
    )
));

我收到错误消息：致命错误：达到最大函数嵌套级别"100"，正在中止！

如何编写此查询？

Answer 1

我会这样做：

SELECT 
    i.id, sum_planned, sum_partial, sum_planned - ifnull(sum_partial,0) AS saldo
FROM
    (SELECT id
    FROM webfactory_sale_invoices
    GROUP BY id
    ) i LEFT OUTER JOIN
    (SELECT 
         id, SUM(ammount) AS sum_planned
    FROM
        webfactory_sale_planned_charges 
    GROUP BY 
        id) pl ON 
        i.id = pl.id LEFT OUTER JOIN        
    (SELECT 
        id, SUM(ammount) AS sum_partial
    FROM
        webfactory_sale_partial_charges 
    GROUP BY 
        id) pa ON i.id = pa.id