Doctrine 2 DQL CASE WHEN in Count

Question

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?

Where exactly are you getting this error ? How do you use your DQL statement ? — Rybus, Jul 04 '14 at 08:04
I'm using it in the class Repository, creating it there and fire it up. The error appears in my frontend. It's part of a filter function, if i'm calling this function a server error appears. Neither stacktrace nor error log provide usefull information :/ — KhorneHoly, Jul 04 '14 at 08:15
How about removing one `(` and `)` as you are using two parenthesis :/ It really seems to be a syntax problem as your query looks good. — Rybus, Jul 04 '14 at 08:20
Tried that, i'm getting the same error again, just instead of `'.'` there's a `expected FROM condition` — KhorneHoly, Jul 04 '14 at 08:21

score 19 · Accepted Answer · edited Oct 25 '22 at 13:30

19

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

edited Oct 25 '22 at 13:30

Marcin Orlowski

72,056
11
123
141

answered Jul 04 '14 at 12:17

KhorneHoly

4,666
6
43
75

Thank you, I couldn't work out where to put CASE, as it would not work in the ORDER clause no matter what I tried! I hope in a future version they make it possible to use in the order clause. – Scott Flack Mar 08 '15 at 23:09
2

Worth noting you can add HIDDEN if you don't want the numImage returned in the results. `SELECT t, SUM(CASE WHEN p.image = 1 then 1 ELSE 0 END) AS HIDDEN numImage` – David Stone Jul 10 '15 at 22:55
Though replacing COUNT with SUM works in your case there seems to be a general problem with using CASE parts in DQL. I opened an issue for it here: https://github.com/doctrine/doctrine2/issues/5915 Feel free to subscribe. – webDEVILopers Jul 03 '16 at 07:45

score 0 · Answer 2 · answered Jul 04 '14 at 07:56

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().

I can't use the NativeQuery because i'll send this data to a bundle which only can work with `Query` objects, but not with `NativeQuery` :-/ — KhorneHoly, Jul 04 '14 at 07:59
Thanks for your help :) I already had the NativeQuery and it was quit frustrating to see that the Bundle can't work with it, but sadly i'm forced to use it — KhorneHoly, Jul 04 '14 at 08:16

Doctrine 2 DQL CASE WHEN in Count

2 Answers2

Linked