Last active
February 15, 2018 16:30
-
-
Save SebLours/7617884 to your computer and use it in GitHub Desktop.
The SortableNullsWalker is a TreeWalker that walks over a DQL AST and constructs the corresponding SQL to allow ORDER BY IS NULL
http://devandco.wordpress.com/2013/11/23/doctrine-mysql-isnull/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace Application\DoctrineExtensions\Query\MySql; | |
use Doctrine\ORM\Query\SqlWalker; | |
use Doctrine\DBAL\DBALException; | |
/** | |
* $query = $qb->getQuery(); | |
* $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, '\Namespace\DoctrineExtensions\Query\MySql\SortableNullsWalker'); | |
* $query->setHint("sortableNulls.fields", array("alias.field" => \Namespace\DoctrineExtensions\Query\MySql\SortableNullsWalker::NULLS_FIRST)); | |
* | |
* @see http://stackoverflow.com/questions/12652034/how-can-i-order-by-null-in-dql | |
* @see http://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last | |
* @see https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/SortableNullsWalker.php | |
*/ | |
class SortableNullsWalker extends SqlWalker | |
{ | |
const NULLS_FIRST = 'NULLS FIRST'; | |
const NULLS_LAST = 'NULLS LAST'; | |
protected $sortableNullsFields; | |
/** | |
* Check if the platform is MySql | |
* @param OrderByClause $orderByClause | |
* @return string | |
*/ | |
public function walkOrderByClause($orderByClause) | |
{ | |
// Store sortable nulls fields | |
$this->sortableNullsFields = $this->getQuery()->getHint('sortableNulls.fields'); | |
// MySql platform ? | |
if (count($this->sortableNullsFields) | |
&& $this->getConnection()->getDatabasePlatform()->getName() != 'mysql' | |
) | |
{ | |
throw DBALException::notSupported('SortableNullsWalker only supported by MySql'); | |
} | |
return parent::walkOrderByClause($orderByClause); | |
} | |
/** | |
* For each item we check if it's an isnull expression | |
* @param orderByItem $orderByItem | |
* @return string | |
*/ | |
public function walkOrderByItem($orderByItem) | |
{ | |
$expr = $orderByItem->expression; | |
$key = $expr->identificationVariable . '.' . $expr->field; | |
if (isset($this->sortableNullsFields[$key]) | |
&& $this->sortableNullsFields[$key] == self::NULLS_FIRST | |
) | |
{ | |
return sprintf( | |
'ISNULL(%s), %s', | |
$this->walkPathExpression($expr), | |
parent::walkOrderByItem($orderByItem) | |
); | |
} | |
return parent::walkOrderByItem($orderByItem); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment