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?

Lodging

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.

Hedelman

Does not works with other types, like Datetime data – Crore

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.

Mongolism

This works perfect! – Hickie

Answer 3

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);

Cacka

thx Thomas, this saved my day. here how to sort with date: ->addSelect('COALESCE(news.date, \'0001-01-01\') AS HIDDEN date')->orderBy('date', 'DESC') – Javed

Answer 4

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

Odo

Answer 5

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

Mordred

Thanks, this seems to be the most sensible action to take, albeit quite convoluted. I see enough instructions on creating the custom function class, but how do I go about importing and implementing inside the QueryBuilder? I have added it to my global config.yml, I assume then I no longer need to import it inside every FormType class then. But how is it used inside the createQueryBuilder call? – Lodging

I have the custom function defined as isnull_string inside my ORM config, and I implement it thusly: $er->createQueryBuilder('airline')->orderBy('isnull_string(airline.codeIata), airline.codeIata', 'ASC'); Which is still giving me a syntax error. Apparently CreateQueryBuilder doesn't support the full DQL syntax as CreateQuery does. Which is a shame since I don't think you can use createQuery inside the FormBuilderInterface (the option is explicitly called query_builder, after all). – Lodging

Answer 6

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!

Prato