How can I order NULL in DQL?

Question

I am building an application using the Symfony2 platform and using Doctrine ORM. I have a table with airlines for which some IATA codes are missing. I have output the list ordered by this IATA code, but I get an undesirable result so that entries with zero IATA codes are sorted at the top.

In MySQL, this is quite simple to do, with ORDER BY ISNULL(code_iata), code_iata , but I do not know what the equivalent will be for DQL. I tried

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

but it gives me a syntax error.

Doctrinal doctrines also do not give me an answer. Is there any way?

Answer 1

DQL does not contain every simple SQL function. Fortunately, you can define your own 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

Answer 2

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

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

The HIDDEN keyword (available after Doctrine 2.2) will cause the inverseWeight field inverseWeight be removed from the result set and thus prevent unwanted mixed results .

(The value of the sort fields is inverted, so the order must also be inverted, so the query uses DESC order, not ASC .)

Credits belong to this answer .

Answer 3

Here is an example of a custom walker to get exactly what you want. I took it from the Doctrine in my problems with github:

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

But the code, as it is, did not work for me in MySQL. I modified it to work in MySQL, but I have not tested it at all for other engines.

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

 <?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

The most unobtrusive general solution would be to use a CASE expression in conjunction 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 

It works with both numeric and other types of fields and does not require a Doctrine extension.

Answer 5

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 Query Builder Doctrine Query Builder (HIDDEN will hide the "freq" field in your query result set.)

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

Answer 6

By default, MySQL will still sort the NULL value; it will simply put it at the beginning of the result set if it was sorted by ASC , and at the end if it was sorted by DESC . Here you want to sort the ASC , but you want the NULL values ​​to be at the bottom.

Unfortunately, as powerful as Doctrine, it will not offer much support here, as function support is limited, and most of it is limited to SELECT , WHERE and HAVING . You really would not have a problem at all if the following were true in QueryBuilder:

• select() accepted ISNULL()
• orderBy() or addOrderBy() supported by ISNULL()
• the class supported the concept of UNION (in this case, you could run two queries: one where codeIata was NULL , and one where it was not, and you can sort each one independently)

So, you can go with the user-defined functions that were already mentioned in ArtWorkAD, or you could replicate this 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 them into one array or independently process them in your templates.

Another idea is to return DQL all to one dataset and allow PHP to do a heavy lift. Sort of:

 $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 disadvantage of two of them is that you cannot do LIMIT in MySQL, so it can only work for relatively small datasets.

Anyway, hope this helps you!