Skip to content

Instantly share code, notes, and snippets.

@SebLours
Last active February 15, 2018 16:30
Show Gist options
  • Save SebLours/7617884 to your computer and use it in GitHub Desktop.
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/
<?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