Doctrine – IF in ORDERBY

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' )
            ->select('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' )
            ->getQuery()
            ;        

And finally – it works!

Btw: doctrine is just stupid