CODEWITHSUNDEEP
phpsql-serverdoctrine-ormprepared-statement
Fractaliste
Fractaliste

Reputation: 5957

How to correctly prepare parameters with DoctrineDBAL on the following query?

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?

Upvotes: 0

Views: 82

Answers (2)

Nicolas Lefebvre
Nicolas Lefebvre

Reputation: 11

yeah! the solution is:

 $qs = $this
    ->createQueryBuilder('h');
    $d = $qs ->select($qs->expr()->count('h'));
    $e =  $d->getQuery()->getScalarResult();
    $qs->addSelect('(COUNT(h.id)*100 / :t) AS percentage')->setParameter('t', $e);
    $qs->addGroupBy(sprintf('h.%s', $type));
    return $qs->getQuery()->getResult();

Upvotes: 0

acontell
acontell

Reputation: 6932

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.

Upvotes: 1

Related Questions