Doctrine - IF in ORDERBY

May 09, 2014 at 10:54
Doctrine Symfony

The problem: there is a table with a field ranking, which is supposed to define a ranking. What this is supposed to do is rank the data in ascending order (ORDER BY ranking ASC), except for those which are NULL. The null entries are supposed to be the last ones. What I want to achieve is something like this:
SELECT * FROM table ORDER BY IF(ranking IS NULL, 9999, ranking) ASC
I tried for hours and hours to achieve this with doctrine, as I would have had to change a lot of stuff if I were to resign to a native query. It just wouldn't work, until someone answered this question. It seems that doctrine cannot do an IF in ORDERBY (...) but what it can do is a CASE WHEN in the select which can be referenced within the order by.
$query = $this
            ->createQueryBuilder( 'a' )
            ->add('from', 'path\to\whatever\table a')
            ->addSelect('CASE WHEN a.ranking IS NULL THEN 9999 ELSE a.ranking END as HIDDEN ORD')
            ->where( 'a.deleted IS NULL' )
            ->orderBy(  'ORD', 'asc' )
And finally - it works! Btw: doctrine is just stupid

Tags: case doctrine if orderby symfony udf

Hinterlasse einen Kommentar: