Les requêtes CTE, ou Common Table Expressions, offrent une approche élégante pour concevoir des requêtes complexes. Introduites avec la version 8.4 de PostgreSQL, elles permettent de créer des requêtes temporaires dont les résultats peuvent être utilisés dans une requête principale.

Les CTE sont définies via la syntaxe WITH alias AS (query), avant de déclarer la requête principale. Plusieurs CTE peuvent être utilisées en même temps, et les résultats des premières peuvent être exploités dans les suivantes.

PostgreSQL

Voici un exemple de CTE en PostgreSQL :

-- CTE listant le montant total des ventes par produit, pour l'année 2023
WITH product_sales AS (
    SELECT
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= '2023-01-01' AND o.order_date < '2024-01-01'
    GROUP BY p.product_id, p.product_name
),

-- CTE classant chaque catégorie en fonction des ventes totales
ranked_categories AS (
    SELECT
        ps.product_id,
        ps.product_name,
        ps.total_sales,
        RANK() OVER (PARTITION BY p.category ORDER BY ps.total_sales DESC) AS category_rank
    FROM product_sales ps
    JOIN products p ON ps.product_id = p.product_id
)

-- Requête principale listant le classement des catégories et leurs produits
SELECT
    pc.product_id,
    pc.product_name,
    pc.total_sales,
    pc.category_rank,
    p.category
FROM ranked_categories pc
JOIN products p ON pc.product_id = p.product_id

Dans cet exemple, deux CTE sont utilisées:

• la première permet de récupérer le volume des ventes par produit
• la seconde classe les catégories en fonction du volume des ventes calculé précédement

Leur utilisation permet notamment:

• optimisation des requêtes complexes les CTE permettent de décomposer les requêtes complexes en parties plus gérables, améliorant la lisibilité du code SQL et facilitant la maintenance.
• amélioration des performances en décomposant une requête en plusieurs parties, les optimisations du plan d'exécution peuvent être mieux exploitées, améliorant ainsi les performances globales des requêtes
• récursivité il est possible d'exécuter les CTE de manière récursive

Doctrine

Malheureusement, cette fonctionnalité n'est pas nativement implentée dans Doctrine à l'heure actuelle (voir https://github.com/doctrine/dbal/issues/5018). Il est tout de même possible de les utiliser en passant par les requêtes natives. Il faudra donc créer un objet ResultSetMapping permettant de définir la structure des résultats, et exécuter le code SQL en faisant appel à la méthode createNativeQuery fournie par l'entityManager:

$rsm = new ResultSetMapping();
$rsm
    ->addScalarResult('product_id', 'product_id')
    ->addScalarResult('product_name', 'product_name')
    ->addScalarResult('total_sales', 'total_sales')
    ->addScalarResult('category_rank', 'category_rank')
    ->addScalarResult('category', 'category');

$query = $this->getEntityManager()->createNativeQuery('
    -- CTE listant le montant total des ventes par produit, pour une plage de date
    WITH product_sales AS (
        SELECT
            p.product_id,
            p.product_name,
            SUM(oi.quantity * oi.unit_price) AS total_sales
        FROM products p
        JOIN order_items oi ON p.product_id = oi.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE o.order_date >= :startRange AND o.order_date < :endRange
        GROUP BY p.product_id, p.product_name
    ),

    -- CTE classant chaque catégorie en fonction des ventes totales
    ranked_categories AS (
        SELECT
            ps.product_id,
            ps.product_name,
            ps.total_sales,
            RANK() OVER (PARTITION BY p.category ORDER BY ps.total_sales DESC) AS category_rank
        FROM product_sales ps
        JOIN products p ON ps.product_id = p.product_id
    )

    SELECT
        pc.product_id,
        pc.product_name,
        pc.total_sales,
        pc.category_rank,
        p.category
    FROM ranked_categories pc
    JOIN products p ON pc.product_id = p.product_id
', $rsm);

$query->setParameter('startRange', '2023-01-01');
$query->setParameter('endRange', '2024-01-01');

return $query->getResult();

Ces exemples ont été créés avec l'éditeur PostgreSQL en ligne https://extendsclass.com/postgresql-online.html.

Les Common Table Expressions sont également disponibles depuis Mysql 8.0, MariaDB 10.2.1 et SQLite 3.8.3.