Doctrine 2 DQL CASE WHEN in Count

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

You need to use ResultSetMappingBuilder. It would look something like :

public function getTurn(){    $rsm = new ResultSetMappingBuilder($this->_em);    $rsm->addRootEntityFromClassMetadata('Foo\BarBundle\Entity\Turn', 't');    $rsm->addJoinedEntityFromClassMetadata('Foo\BarBundle\Entity\Poi', 'p', 't', 'poi', array('id' => 'poi_id'));           $rsm->addScalarResult('ImageCount', 'ImageCount');    $sql = 'SELECT t.id, t.foo, t.bar,            SUM(CASE WHEN p.image = 1 then 1 else null end) ImageCount,                            FROM Turn t               INNER JOIN poi p ON t.id = p.turn_id                            ORDER BY ImageCount DESC';    $query = $this->_em->createNativeQuery($sql, $rsm);    return $query->getScalarResult();}

note: you might need to change $query->getScalarResult()to $query->getResult().