如何在以下查询中使用DoctrineDBAL正确准备参数？

Question

I need to get the percentage of each possible values in the field column, over the total value of my table.

I found two way to get my result in SQL:

SELECT m.field, sum(m.value) * 100 / t.total
FROM my_table AS m 
    CROSS JOIN (
        SELECT SUM(value) AS total FROM 
        WHERE year = 2000) t
WHERE m.year = 2000
GROUP BY m.field, t.total

And

SELECT m.field, sum(m.value) * 100 / (SELECT SUM(value) AS total FROM WHERE year = 2000)
FROM my_table AS m 
WHERE m.year = 2000
GROUP BY m.field

But both are nested queries, and I don't know how to prepare statments with the Doctrine's QueryBuilder into a nested queries.

Is there a way to do it?

Answer 1

I have been trying to do so using querybuilder and DQL with no success. As it seems, DQL doesn't allow operations with subqueries in SELECT. What I've achieved so far:

$subQuery = $em->createQueryBuilder('m')
                ->select("SUM(m.value)")
                ->where("m.year = 2000")
                ->getDQL();

The following query works though doesn't calculate the percentage:

$query = $em->createQueryBuilder('f')
                ->select("f.field")
                ->addSelect(sprintf('(%s) AS total', $subQuery))
                ->addSelect('(SUM(f.value)*100) AS percentage')
                ->where("f.year = 2000")
                ->groupBy("f.field")
                ->getQuery()
                ->getResult();

However, if you try to add the division in the select in order to get the percentage and you use the subquery, it simply doesn't work. Looks like the construction it's not allowed in DQL. I've tried with an alias and with the subquery directly and neither of them worked.

Doesn't work:

$query = $em->createQueryBuilder('f')
                    ->select("f.field")
                    ->addSelect(sprintf('(%s) AS total', $subQuery))
                    ->addSelect('(SUM(f.value)*100)/total AS percentage')
                    ->where("f.year = 2000")
                    ->groupBy("f.field")
                    ->getQuery()
                    ->getResult();

Doesn't work either:

$query = $em->createQueryBuilder('f')
                    ->select("f.field")
                    ->addSelect(sprintf('(SUM(f.value)*100)/(%s) AS percentage', $subQuery))
                    ->where("f.year = 2000")
                    ->groupBy("f.field")
                    ->getQuery()
                    ->getResult();

I'd suggest using SQL directly (Doctrine allows it). Using native sql queries and mapping the results would do the trick. There is no disadvantage in doing so.

Documentation

If you find a way of doing it using queryBuilder or DQL, please let me know.

Hope it helps.