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.

Answer 3

Here it is an example for a custom walker to get exactly what you want. I have taken it from Doctrine in its github issues:

https://github.com/doctrine/doctrine2/pull/100

But the code as it is there didn't work for me in MySQL. I have modified it to work in MySQL, but I haven't test at all for other engines.

Put following walker class for example in YourNS\Doctrine\Waler\ directory;

<?php

namespace YourNS\Doctrine\Walker;

use Doctrine\ORM\Query\SqlWalker;

class SortableNullsWalker extends SqlWalker
{
   const NULLS_FIRST = 'NULLS FIRST';
   const NULLS_LAST = 'NULLS LAST';

   public function walkOrderByClause($orderByClause)
   {
      $sql = parent::walkOrderByClause($orderByClause);

      if ($nullFields = $this->getQuery()->getHint('SortableNullsWalker.fields'))
      {
         if (is_array($nullFields))
         {
            $platform = $this->getConnection()->getDatabasePlatform()->getName();
            switch ($platform)
            {
            case 'mysql':
               // for mysql the nulls last is represented with - before the field name
               foreach ($nullFields as $field => $sorting)
               {
                  /**
                   * NULLs are considered lower than any non-NULL value,
                   * except if a – (minus) character is added before
                   * the column name and ASC is changed to DESC, or DESC to ASC;
                   * this minus-before-column-name feature seems undocumented.
                   */
                  if ('NULLS LAST' === $sorting)
                  {
                     $sql = preg_replace_callback('/ORDER BY (.+)'.'('.$field.') (ASC|DESC)/i', function($matches) {
                        if ($matches[3] === 'ASC') {
                           $order = 'DESC';
                        } elseif ($matches[3] === 'DESC') {
                           $order = 'ASC';
                        }
                        return ('ORDER BY -'.$matches[1].$matches[2].' '.$order);
                     }, $sql);
                  }
               }
                  break;
            case 'oracle':
            case 'postgresql':
               foreach ($nullFields as $field => $sorting)
               {
                  $sql = preg_replace('/(\.' . $field . ') (ASC|DESC)?\s*/i', "$1 $2 " . $sorting, $sql);
               }
               break;
            default:
               // I don't know for other supported platforms.
               break;
               }
            }
         }

         return $sql;
   }
}

Then:

use YourNS\Doctrine\Walker\SortableNullsWalker;
use Doctrine\ORM\Query;

[...]

$qb = $em->getRepository('YourNS:YourEntity')->createQueryBuilder('e');
$qb
   ->orderBy('e.orderField')
   ;

$entities = $qb->getQuery()
  ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER,  '\YourNS\Doctrine\Walker\SortableNullsWalker')
  ->setHint('SortableNullsWalker.fields', array(
     'sortOrder' => SortableNullsWalker::NULLS_LAST
  ))
  ->getResult();

Answer 4

If you want to do something similar to "NULLS LAST" in SQL (with PostgreSQL in my case):

ORDER BY freq DESC NULLS LAST

You can use the COALESCE function with the Doctrine Query Builder (HIDDEN will hide the field "freq" on your query result set).

$qb = $this->createQueryBuilder('d')
           ->addSelect('COALESCE(d.freq, 0) AS HIDDEN freq')
           ->orderBy('freq', 'DESC')
           ->setMaxResults(20);

Answer 5

By default, MySQL will still sort a NULL value; it will just place it at the beginning of the result set if it was sorted ASC, and at the end if it was sorted DESC. Here, you're looking to sort ASC, but you want the NULL values to be at the bottom.

Unfortunately, as powerful as it is, Doctrine isn't going to offer much support here, since function support is limited, and most of it is limited to SELECT, WHERE, and HAVING clauses. You actually wouldn't have a problem at all if any of the following were true about the QueryBuilder:

• select() accepted ISNULL()
• orderBy() or addOrderBy() supported ISNULL()
• the class supported the concept of UNIONs (with this, you could run two queries: one where the codeIata was NULL, and one where it wasn't, and you could sort each independently)

So that said, you can go with the user-defined functions that ArtWorkAD mentioned already, or you could replicate that last point with two different Doctrine queries:

$airlinesWithCode = $er->createQueryBuilder("airline")
    ->where("airline.iataCode IS NULL")
    ->getQuery()
    ->getResult();
$airlinesWithoutCode = $er->createQueryBuilder("airline")
    ->where("airline.iataCode IS NOT NULL")
    ->getQuery()
    ->getResult();

Then you can combine these into a single array, or treat them independently in your templates.

Another idea is to have DQL return everything in one data set, and let PHP do the heavy lifting. Something like:

$airlines = $er->findAll();
$sortedAirlines = array();
// Add non-NULL values to the end if the sorted array
foreach ($airlines as $airline)
    if ($airline->getCodeIata())
        $sortedAirlines[] = $airline;
// Add NULL values to the end of the sorted array
foreach ($airlines as $airline)
    if (!$airline->getCodeIata())
        $sortedAirlines[] = $airline;

The downside to both of these is that you won't be able to do LIMITs in MySQL, so it might only work well for relatively small data sets.

Anyway, hope this gets you on your way!

Answer 6

DQL does not contain every function of plain SQL. Fortunately you can define your custom DQL method to accomplish this.

Some resources:

http://punkave.com/window/2012/07/24/for-the-php-crowd-adding-custom-functions-to-doctrine-2-dql

http://docs.doctrine-project.org/en/2.1/cookbook/dql-user-defined-functions.html

http://symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.html