Doctrine2 - Specific NULL Sort

Question

When you sort by column a in descending order, it will put all records with a NULL at the bottom, which makes sense. I want the following order when I sort the attribute a in descending order:

 a ---- NULL NULL 5 4 3 2 1 

I know that Oracle has NULLS_FIRST , and in MySQL you can use ORDER BY ISNULL(a) , but I am wondering if there is an elegant way to handle this using only the Doctrine2 function? Therefore, do not use your own queries, etc.

---

Answer using S0pa: I fixed it using the following new node function for Doctrine2:

 <?php namespace MyBundle\General; use \Doctrine\ORM\Query\Lexer; class IsnullFunctionNode extends \Doctrine\ORM\Query\AST\Functions\FunctionNode { private $isnull; public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->isnull = $parser->ArithmeticPrimary(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'ISNULL('.$this->isnull->dispatch($sqlWalker).')'; } } 

And connected it in Symfony2 with Doctrine2 as follows (app.yml):

 doctrine: orm: entity_managers: default: ... dql: numeric_functions: isnull: Diagenda\CommonBundle\General\IsnullFunctionNode 

I cannot use these functions in my ORDER BY , but by doing SELECT ISNULL(a) AS n_a , I can order the n_a attribute.

Answer 1

I think the best way is to define your own DQL function using this tutorial . You still have to handle various cases depending on your database management system.