Faire des requêtes CTE avec Doctrine ORM en PHP

J’ai déjà évoqué sur ce blog que le développement “moderne” avec les ORM masque les fonctionnalités avancées des SGBD au point que dorénavant les développeurs ne maitrisent et ne connaissent guère plus que le classique SELECT ... FROM ... WHERE ....

Dans les mécanismes méconnus et qui pourtant, pourrait permettre de soulager certains traitements applicatifs, on retrouve les CTE (Common Table Expressions). Voyons comment les utiliser avec Doctrine ORM en PHP.

Commençons par expliquer ce qu’est une CTE. Il s’agit d’une sous-requête utilisable comme une table temporaire. C’est une technique utile pour décomposer des requêtes complexes, éviter la duplication de sous-sélections ou écrire des requêtes récursives.

En presque 20 ans d’expérience professionnelle, j’ai très rarement eu l’occasion d’en voir dans le code que je peux être amené à parcourir quotidiennement. Prenons par exemple, un blog où les articles sont rattachés à des catégories, ces dernières organisées sous forme arborescente sous la forme Category(id, label, parent_id). Je suis certain que, si l’on demande à un développeur de récupérer toutes les catégories parentes d’une catégorie précise, ce dernier fera le traitement en PHP avec un code ressemblant au suivant:

// src/Repository/CategoryRepository.php
class CategoryRepository extends ServiceEntityRepository
{
    // ...

    /**
     * @return list<Category>
     */
    function getCatagoriesWithParents(int $categoryId): array
    {
        $category = $this->categoryRepository->find($categoryId);

        $categories = [
            $category,
        ];

        while ($parent = $category->getParent()) {
            $categories[] = $parent;
        }

        return $categories;
    }
}

L’inconvénient majeur ici est qu’en interne, Doctrine va faire une requête à chaque tour de boucle. C’est ce que l’on appelle le problème N+1. Pour résoudre ce problème, via une requête SQL, il n’est pas possible de faire un simple SELECT ... FROM ... WHERE, c’est exactement dans ce cas qu’une CTE récursive est utile. Cette dernière peut être écrite de la manière suivante:

WITH RECURSIVE cte_category AS (
    -- point de départ
    SELECT id, label, parent_id, 0 AS depth
    FROM category
    WHERE id = :id

    UNION ALL

    -- récursivité
    SELECT c.id, c.label, c.parent_id, cp.depth + 1
    FROM category c
    INNER JOIN cte_category cp ON c.id = cp.parent_id
)
SELECT id, label, parent_id FROM cte_category
ORDER BY depth DESC

La requête commence par récupérer la catégorie identifiée par :id, puis se joint récursivement à elle-même en remontant via parent_id jusqu’à ce qu’il n’y ait plus de parent. La colonne depth permet ensuite de trier du plus ancien ancêtre jusqu’à la catégorie cible.

Le problème pour faire cette requête avec un ORM tel que Doctrine, c’est que ce dernier ne permet pas de gérer les CTE nativement au travers de son QueryBuilder ni même en DQL. Il est alors nécessaire de passer par une requête native où le résultat final sera mappé sur un objet.

// src/Repository/CategoryRepository.php
class CategoryRepository extends ServiceEntityRepository
{
    // ...

    public function findAllWithParents(int $categoryId)
    {
        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata(Category::class, 'c');

        $sql = <<<SQL
            WITH RECURSIVE with_categories AS (
                SELECT id, label, parent_id, 0 AS depth
                FROM category
                WHERE id = :id

                UNION ALL

                SELECT c.id, c.label, c.parent_id, cp.depth + 1
                FROM category c
                INNER JOIN with_categories cp ON c.id = cp.parent_id
            )
            SELECT id, label, parent_id FROM with_categories
            ORDER BY depth DESC
        SQL;

        return $this->getEntityManager()
            ->createNativeQuery($sql, $rsm)
            ->setParameter('id', $categoryId)
            ->getResult();
    }
}

Dans le code précédent, le ResultSetMappingBuilder se charge de faire correspondre les colonnes retournées avec les propriétés de l’entité Category. On obtient alors en sortie une liste d’instances de Category ordonnés de la racine jusqu’à la catégorie demandée.

Avec ce type de requête, on peut faire énormément de choses, il est par exemple, possible de récupérer tous les articles appartenant à une catégorie enfant en réutilisant la requête précédente:

WITH RECURSIVE category_path AS (
    SELECT id, label, parent_id
    FROM category
    WHERE id = :id

    UNION ALL

    SELECT c.id, c.label, c.parent_id
    FROM category c
    INNER JOIN category_path cp ON c.id = cp.parent_id
)
SELECT a.id, a.title, a.category_id
FROM article a
INNER JOIN category_path cp ON a.category_id = cp.id
ORDER BY a.published_date DESC

En une seule requête, on récupère tous les articles liés à la catégorie cible ou à n’importe lequel de ses ancêtres. Sans CTE récursive, il faudrait soit faire plusieurs requêtes successives pour parcourir la hiérarchie une approche bien moins efficace. Un point d’attention néanmoins, étant donné que l’on exécute une requête SQL native, il faudra faire attention à la portabilité de cette dernière, les CTE pouvant ne pas être disponible sur toutes les bases de données supportées par Doctrine.

De plus, l’utilisation du ResultSetMappingBuilder nécessite que le SELECT de la requête contienne l’ensemble des colonnes nécessaire à l’alimentation de l’objet. À défaut, l’entité sera hydratée de manière incomplète sans qu’aucune erreur explicite ne soit levée.