Doctrine DQL query with sub queries sum

Question

The following Doctrine DQL query returns a syntax error :

    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();

I would like a "simple" sum of 2 sub queries like this valid MySQL statement

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;

How can I do this with DQL?

*"How can I do this with DQL?"* The easy way cheat define a view and query that instead as DQL does not support all valid SQL syntax.. .. — Raymond Nijland, Nov 06 '19 at 13:38
@RaymondNijland I thought about this kind of workaround but I didn't find a way to call a view in a DQL statement. Do you? — sdespont, Nov 06 '19 at 13:42
... i wonder your SQL has a (dual) corelated subquery, which is most of the time slow in SQL which might be the reason that it is not supported.. So you can consider rewriting this as a JOIN instead.. — Raymond Nijland, Nov 06 '19 at 13:43
*"I thought about this kind of workaround but I didn't find a way to call a view in a DQL statement. Do you?"* Move away from your pc, get some coffee and think how you would normally use a view in a SQL IDE? ... — Raymond Nijland, Nov 06 '19 at 13:45
I think duplicate - https://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2 — Dmitry Leiko, Nov 06 '19 at 14:35

score 0 · Answer 1 · answered Nov 08 '19 at 21:19

Unfortunately, doctrine`s parser cannot combine 2 subselects into 1 computed field.

Your best option is to split the subselects:

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();

and combine the 2 sums produced.

Alternatively, you could rewrite the query and use 2 JOINs with "PaymentTableA p1" and "PaymentTable2 p2":

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

Doctrine DQL query with sub queries sum

1 Answers1