mysql - 带子查询和的 Doctrine DQL 查询

Question

以下 Doctrine DQL 查询返回语法错误:

    return $this->getEntityManager()->createQuery(
        'SELECT i invoice, (
            (SELECT SUM(p1.amount) FROM PaymentTableA p1 WHERE p1.invoice = i.id)
            +
            (SELECT SUM(p2.amount) FROM PaymentTable2 p2 WHERE p2.invoice = i.id)
        ) mySUM            
        FROM Invoice i
        WHERE i.id BETWEEN 1 AND 50'
    )->getResult();

我想要一个 2 个子查询的“简单”总和，就像这个有效的 MySQL 语句

select i.*, (
  (select sum(p1.amount) from PaymentTable1 p1 where p1.invoice_id = i.id)
  +  
  (select sum(p2.amount) from PaymentTable2 p2 where p2.invoice_id = i.id)
) mySUM            
from Invoice i
where i.id between 1 and 50;

如何使用 DQL 执行此操作？

Answer 1

不幸的是，doctrine 的解析器无法将 2 个子选择合并到 1 个计算字段中。

最好的选择是拆分子选择:

return $this->getEntityManager()->createQuery(
    'SELECT i invoice,
        (SELECT SUM(p1.amount) FROM PaymentTableA p1 
                    WHERE p1.invoice = i.id) AS sum1
        (SELECT SUM(p2.amount) FROM PaymentTable2 p2 
                    WHERE p2.invoice = i.id) As sum2
    FROM Invoice i
    WHERE i.id BETWEEN 1 AND 50'
)->getResult();

并结合产生的 2 个和。

或者，您可以重写查询并使用“PaymentTableA p1”和“PaymentTable2 p2”使用 2 个 JOIN:

SELECT i invoice,
    SUM(p1.amount) + SUM(p2.amount) AS MySum
FROM Invoice i
JOIN PaymentTableA p1 WITH p1.invoice = i.id
JOIN PaymentTable2 p2 WITH p2.invoice = i.id
WHERE i.id BETWEEN 1 AND 50
GROUP BY i.id