How can I order by NULL in DQL?

Question

I'm building an app using Symfony2 framework and using Doctrine ORM. I have a table with airlines for which some IATA codes are missing. I'm outputting a list, ordered by this IATA code, but I'm getting the undesirable result that the records with null IATA codes are sorted at the top.

In MySQL this is simple enough to do, with ORDER BY ISNULL(code_iata), code_iata but I'm clueless as to what the equivalent would be for DQL. I tried

$er->createQueryBuilder('airline')->orderBy('ISNULL(airline.codeIata), airline.codeIata', 'ASC')

but this gives me a syntax error.

The Doctrine docs give me no answer either. Is there a way?

Answer 1

You can use the following trick in DQL to order NULL values last

$em->createQuery("SELECT c, -c.weight AS HIDDEN inverseWeight FROM Entity\Car c ORDER BY inverseWeight DESC");

The HIDDEN keyword (available since Doctrine 2.2) will result in omitting the inverseWeight field from the result set and thus preventing undesirable mixed results.

(The sort fields value is inverted therefore the order has to be inverted too, that's why the query uses DESC order, not ASC.)

Credits belong to this answer.

Answer 2

The most unobtrusive generic solution would be to use the CASE expression in combination with the HIDDEN keyword.

   SELECT e,
     CASE WHEN e.field IS NULL THEN 1 ELSE 0 END HIDDEN _isFieldNull
     FROM FooBundle:Entity e
 ORDER BY _isFieldNull ASC

Works with both numeric as well as other field types and doesn't require extending Doctrine.