Trier avec les valeurs NULL a la fin dans une requete Doctrine

Si vous êtes comme moi et que souhaitez trier sur un champ, disons `dueDate`, mais avec la particularité que le tri place les valeur NULL de ce champ en fin de liste, cet article vous sera utile. MySQL tri par défaut les valeur NULL en début de liste, ce qui est gênant par exemple si vous voulez trier par date croissante avec les lignes dont la date est nulle en fin de liste.

Le principe de la solution présentée si dessous est donc d’utiliser un « hint » sur la requête et de créer une classe héritée de la classe Doctrine\ORM\Query\SqlWalker permettant de réécrire la requête.

Code de la classe :

namespace DoctrineExtensions\CustomWalker;

use Doctrine\ORM\Query\SqlWalker;

/**
 * SortableNullsWalker
 */
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('/\s+([a-z0-9_]+)(\.' . $field . ') (ASC|DESC)?\s*/i', " ISNULL($1$2), $1$2 $3 ", $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;
	}
}




Et pour l’utiliser, voici un exemple :

use Doctrine\ORM\Query;
use Doctrine\ORM\EntityRepository;
use DoctrineExtensions\CustomWalker\SortableNullsWalker;

[...]

$qb = $em->createQueryBuilder();
$qb->select('t')
	->from('Task', 't')
	->addOrderBy('t.dueDate', 'ASC');

$query = $qb->getQuery();
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'DoctrineExtensions\CustomWalker\SortableNullsWalker')
	->setHint('SortableNullsWalker.fields', array(
		'dueDate' => SortableNullsWalker::NULLS_LAST,
	)
);




Attention: le code ci-dessus a été écrit pour MySQL, Oracle et PostgreSQL. Il n’a été testé qu’avec MySQL.

Pour info, ce post m’a été utile sur Doctrine-project.

Cette entrée a été publiée dans Tip, avec comme mot(s)-clef(s) , , . Vous pouvez la mettre en favoris avec ce permalien.