mysqlsymfonydoctrine-ormdql

Doctrine 2 DQL CASE WHEN in Count


I have this Query in native MySQL Code

SELECT *
FROM `turn`
LEFT JOIN (
    poi
) ON ( turn.id = poi.turn_id )
GROUP BY turn.id
ORDER BY count( case when poi.image = 1 then 1 else null end) DESC;

I need to rebuild this in Doctrine 2 DQL

My attempt so far is this:

SELECT t, COUNT((CASE WHEN Bundle\Entity\Poi p.image = 1 then 1 ELSE NULL END)) AS num
FROM Bundle\Entity\Turn t
JOIN t.pois p
GROUP BY t.id
ORDER BY num DESC

And im getting this error:

An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 99: Error: Expected end of string, got '.'") in Bundle:Admin:showTurnsFiltered.html.twig at line 75.

What am i doing wrong?


Solution

  • I found it by myself after hours of trying and searching, it's working with this DQL:

    $dql = 'SELECT t, 
                SUM(CASE WHEN p.image = 1 THEN 1 ELSE 0 END) AS numImage
            FROM Bundle\Entity\Turn t
                JOIN t.pois p
            GROUP BY t.id
            ORDER BY numImage DESC';  
    

    Important that you need to use SUM instead of COUNT