Doctrine 2 DQL CASE WHEN in Count

doctrine-orm, dql, mysql, symfony

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`

Problem

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?

Original source