Utiliser le DISTINCT ON PostgreSQL avec Doctrine ORM (Symfony)

Il y’a quelques temps, dans un projet Symfony + API platform avec SGBD Postgre, j’ai eu besoin de faire un DISTINCT ON. Ça parait simple dit comme ça, mais ça n’a pas été une promenade de santé.

Sommaire masquer

1 Pourquoi DISTINCT ON ?
2 Avec un query builder, ça devrait être facile ?
3 Les custom functions de Doctrine
- 3.1 Le problème avec DISTINCT ON
4 Les étapes de génération d’une requête SQL
5 Les Hint et SQL walkers
- 5.1 Les hints doctrine
- 5.2 Le SQL walker
6 Appeler notre DISTINCT ON depuis un controller

Pourquoi DISTINCT ON ?

Prenons une requête toute simple, avec pour seule particularité d’avoir un ORDER BY. Et bien en Postgresql, pour appliquer un DISTINCT sur un champ présent dans la clause ORDER BY, vous devez impérativement passer par un DISTINCT ON.

// Cette requête ne marche pas en PostgreSQL => renvoit toutes les lignes y compris les doublons
SELECT DISTINCT account_id, id, email FROM user ORDER BY account_id
// Cette requête marche => filtre les doublons
SELECT DISTINCT ON (account_id) account_id, id, email FROM user ORDER BY account_id

Comme on le voit dans l’exemple ci-dessus, impossible d’utiliser un simple DISTINCT dans notre cas.

Avec un query builder, ça devrait être facile ?

Mon premier réflexe après avoir compris que je devais utiliser un DISTINCT ON à été de me tourner vers la doc de Doctrine, / Symfony pour trouver comment l’utiliser en DQL. Ça ne devrait pas être si compliqué, si ?

Et bien détrompez-vous. DISTINCT ON est une spécificité du moteur Postgre, ça n’existe pas en SQL, ni en MySQL par exemple. Doctrine essayant de coller au plus prêt au standard SQL, il n’y a pas ou très peu d’implémentations de fonctionnalités spécifiques à tel ou tel SGBD.

De ce fait, il n’existe pas de manière simple de l’appeler dans un query builder.

Les custom functions de Doctrine

Doctrine se veut malgré tout très extensible, et ce qu’il n’est pas possible de faire avec les fonctions basiques d’un query builder, on peut le surcharger de plusieurs manières. La manière la plus simple et la plus logique est de se créer des custom functions.

Une custom function permet de définir une fonction personnalisée qui peut ensuite être appelée dans n’importe quelle requête DQL.

Voici un exemple d’implémentation pour faire la soustraction de deux dates :

<?php
/**
 * DateDiffFunction ::= "DATEDIFF" "(" ArithmeticPrimary "," ArithmeticPrimary ")"
 */
class DateDiff extends FunctionNode
{
    // (1)
    public $firstDateExpression = null;
    public $secondDateExpression = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(TokenType::T_IDENTIFIER); // (2)
        $parser->match(TokenType::T_OPEN_PARENTHESIS); // (3)
        $this->firstDateExpression = $parser->ArithmeticPrimary(); // (4)
        $parser->match(TokenType::T_COMMA); // (5)
        $this->secondDateExpression = $parser->ArithmeticPrimary(); // (6)
        $parser->match(TokenType::T_CLOSE_PARENTHESIS); // (3)
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'DATEDIFF(' .
            $this->firstDateExpression->dispatch($sqlWalker) . ', ' .
            $this->secondDateExpression->dispatch($sqlWalker) .
        ')'; // (7)
    }
}

On comprend qu’on a une fonction parse qui essaie de matcher la syntaxe de notre fonction:

L’identifier de la fonction (DATEDIFF)
Une parenthèse ouverte
Une première expression de date
Une virgule
Une deuxième expression de date
Une parenthèse fermante

On peut ensuite produire du DQL qui ressemble à ça par exemple :

SELECT p FROM DoctrineExtensions\Query\BlogPost p WHERE DATEDIFF(CURRENT_TIME(), p.created) < 7

Chouette, on va pouvoir créer une custom function DISTINCT_ON(...) alors ??

Le problème avec DISTINCT ON

Et bien non… La clause DISTINCT ON de Postgre a une particularité syntaxique très spéciale dans la manière dont elle est écrite interprétée. Reprenons la requête du départ ainsi que quelques lignes de résultat :

SELECT DISTINCT ON (account_id) account_id, id, email FROM user ORDER BY account_id

account_id, id, email
1         , 20, email1@mail.com
2         , 21, email2@mail.com
3         , 23, email3@mail.com

Ici, on voit deux choses :

On n’a pas de doublon sur l’account_id
et la première colonne de donnée correspond au champ account_id

Maintenant prenons cette requête légèrement modifiée :

SELECT DISTINCT ON (account_id) firstname, id, email, account_id FROM user ORDER BY account_id

firstname, id, email          , account_id
Bob      , 20, email1@mail.com, 1
Bar      , 21, email1@mail.com, 2
Foo      , 23, email1@mail.com, 3

Le premier champ contient bien des prénoms et n’est pas du tout un alias du DISTINCT ON, alors qu’il n’est pas séparé de ce dernier par une virgule.

Et c’est là tout le problème. Pour faire court, la manière dont Doctrine parse le DQL d’un select c’est en traitant chaque ensemble séparé par des virgules comme une colonne du SELECT. Or, ici il va essayer d’interpréter DISTINCT ON (account_id) firstname comme un champ à part entière (une fonction avec un alias) alors que ce n’est pas du tout le cas.

La manière dont le DQL est construit par le query builder, puis parsé pour produire le SQL final ne permet pas de faire marcher une custom function produisant un DISTINCT ON.

Les étapes de génération d’une requête SQL

Avant de continuer, prenons de la hauteur sur le processus de génération d’une requête SQL avec Doctrine. Il se passe en 3 grandes étapes :

Construction d’un query builder
- Potentiellement avec appel de custom functions
Génération du DQL de la requête, une fois le query builder complété (transformation des custom functions, …)
Parcours du DQL pour générer le SQL final pour le SGBD cible (Postgre, SQL server, …)

Les Hint et SQL walkers

On a vu qu’on ne pouvait pas agir sur les deux premières étapes pour intégrer notre DISTINCT ON à la requête, mais il nous reste une 3ème possibilité.

En creusant encore la problématique, j’ai finis par découvrir l’existante des hints (trad: indices) et des SQL walkers. Ces derniers permettent de se brancher pour agir sur la toute dernière étape de génération d’une requête.

Les hints doctrine

Les hints permettent de passer des infos à une query pour potentiellement agir sur la manière dont le SQL est généré. On peut aussi spécifier le walker (voir ci-dessous) à utiliser pour générer le SQL.

Dans notre cas, on va vouloir spécifier le ou les champs à passer au DISTINCT ON, pour que le walker puisse écrire le SQL correspondant. Pour reprendre notre exemple, ça pourrait ressembler à ça :

$query = $queryBuilder->getQuery();
$query->setHint(CustomOutputWalker::HINT_FIELDS_DISTINCT_ON, ['user.account_id']);

Voir la suite pour comprendre le CustomOutputWalker::HINT_FIELDS_DISTINCT_ON

Le SQL walker

Doctrine utilise son SQL walker pour parcourir le DQL et générer le SQL correspondant. On peut étendre cette classe pour interagir avec le SQL directement au moment de sa génération. On peut donc imaginer le walker suivant :

class CustomOutputWalker extends Query\SqlWalker
{
    public const HINT_FIELDS_DISTINCT_ON = 'distinctOn.fields';


    public function walkSelectClause($selectClause)
    {
        $sql = parent::walkSelectClause($selectClause);

        if (($hint = $this->getQuery()->getHint(self::HINT_FIELDS_DISTINCT_ON)) && !str_contains($sql, 'DISTINCT')) {
            $distinctOn = 'DISTINCT ON (';

            foreach ($hint as $key => $field) {
                $alias = explode('.', $field)[0];
                $column = explode('.', $field)[1] ?? null;
                if ($column) {
                    // Table alias
                    $tableIdentifier = $this->walkIdentificationVariable($alias, $column);
                    $distinctOn .= $tableIdentifier;

                    // Column alias
                    $distinctOn .= '.' . $column;
                } else {
                    $distinctOn .= $this->walkResultVariable($alias);
                }
                if ($key < (is_countable($hint) ? count($hint) : 0) - 1) {
                    $distinctOn .= ', ';
                }
            }

            $distinctOn .= ') ';
            $sql = str_replace('SELECT ', 'SELECT ' . $distinctOn, $sql);
        }

        return $sql;
    }
}

On exploite les hint pour savoir sur quels colonnes on doit appliquer le DISTINCT ON. On vérifie aussi que la requête ne contienne pas déjà un DISTINCT « natif » car on ne veut pas générer de conflits.

Ensuite, on construit notre clause en utilisant les colonnes spécifiées dans le hint. Je n’entrerais pas dans les détails mais on va à chaque fois chercher l’alias correspondant généré à partir du DQL. C’est un peu complexe et ça m’a demandé beaucoup beaucoup d’essais et de reverse-engineering pour en arriver là.

Appeler notre DISTINCT ON depuis un controller

Doctrine lit quelques hints avec des noms standards pour générer ses requêtes, il en existe notamment un pour spécifier le walker à utiliser : Query::HINT_CUSTOM_OUTPUT_WALKER

On va donc utiliser tout ce qu’on a appris pour appliquer notre walker et donc le DISTINCT ON sur les bons champs dans notre requête :

// Construction du query builder avec order by etc...
// ...
$query = $queryBuilder->getQuery();
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CustomOutputWalker::class);
$query->setHint(CustomOutputWalker::HINT_FIELDS_DISTINCT_ON, ['user.account_id']);

Si vous avez appliqué tout ce qu’on a vu ensemble dans votre projet, ça devrait marcher et générer la requête correctement !

Facebook Tweet Pin LinkedIn