CTE : la clause WITH que les ORM ignorent (mais que vous devriez connaître)

Les CTE (Common Table Expressions) permettent d’écrire des requêtes SQL complexes de façon lisible et modulaire. Présentes dans PostgreSQL, Oracle ou MySQL 8+, elles sont pourtant ignorées par la majorité des ORM : Doctrine, Hibernate ou Eloquent ne les prennent tout simplement pas en charge. Petit éclairage technique.

Cet article a été mis à jour le 19 juillet 2025 ; il prend 16 minutes à lire et comporte 3893 mots.

Je suis abon­né à Medi­um, on y trou­ve d’ex­cel­lents arti­cles, mais hélas trop sou­vent réservés aux abon­nés, dont un qui relate un cas con­cret d’u­til­i­sa­tion des CTE. J’ai pu en lire les pre­mières lignes, j’en ai eu l’eau à la bouche… mais 50$ l’abon­nement… même si c’est par an, en addi­tion­nant tous les abon­nements que l’on pour­rait pren­dre, on arrive vite à des sommes astronomiques. Alors je suis resté sur ma faim.

Enfin, pas trop longtemps. D’ac­cord, je n’ai pas lu l’ar­ti­cle. Mais je me suis doc­u­men­té, et j’ai com­pris tout l’in­térêt des CTE. Et je vais vous en par­ler. Et même vous don­ner un cas d’usage, certes fic­tif, mais qui illus­tre bien les béné­fices que l’on peut en tirer.

Les Com­mon Table Expres­sions (CTEs), ou claus­es WITH, sont un out­il puis­sant et élé­gant pour struc­tur­er des requêtes SQL com­plex­es sans se per­dre dans des sous-requêtes imbriquées à rallonge.

Longtemps absentes de MySQL, elles sont pour­tant présentes depuis longtemps dans des SGBD comme Post­greSQL, SQL Serv­er, Db2 ou Ora­cle. Depuis MySQL 8.0, elles y ont été inté­grées, mais restent encore sous-util­isées, sou­vent ignorées par les ORM.

Pour­tant, maîtris­er les CTE, c’est sim­pli­fi­er la lec­ture, amélior­er la main­ten­abil­ité et plus prag­ma­tique­ment opti­miser les per­for­mances de vos requêtes. Que vous soyez fatigué de jon­gler avec des requêtes illis­i­bles ou que vous souhaitiez exploiter pleine­ment la puis­sance de votre base, les CTE méri­tent qu’on s’y intéresse sérieusement.

Mais avant tout, nous allons faire…

Un peu d’histoire

L’adop­tion des CTE dans les prin­ci­pales bases de don­nées SQL, sans être vrai­ment récente, ne remonte pas non plus à Math­usalem – surtout quand on sait que cer­taines de ces bases de don­nées sont sor­ties dans les années 70 :

Pourquoi les CTE ?

Les requêtes SQL clas­siques bour­rées de sous-requêtes, c’est un cauchemar pour lire, com­pren­dre et maintenir.

Prenons un exem­ple simple :

SELECT * FROM users WHERE id IN (
  SELECT DISTINCT user_id FROM orders WHERE order_date > '2025-01-01'
);

Ça fonc­tionne, mais si la sous-requête se com­plex­i­fie, ça devient vite illis­i­ble. Avec une CTE, on peut écrire :

WITH recent_orders AS (
  SELECT DISTINCT user_id FROM orders WHERE order_date > '2025-01-01'
)
SELECT * FROM users WHERE id IN (SELECT user_id FROM recent_orders);

ou mieux, avec un JOIN :

WITH recent_orders AS (
  SELECT DISTINCT user_id FROM orders WHERE order_date > '2025-01-01'
)
SELECT users.*
FROM users
JOIN recent_orders ON users.id = recent_orders.user_id;

Le résul­tat est le même, mais le code est plus clair, plus facile à décomposer.

Voici une représen­ta­tion graphique (sim­pli­fiée) :

flowchart TD
    A["Début de la requête"] --> B["Définition de la CTE<br>WITH nom_cte AS (...)"]
    B --> C["Contenu de la CTE<br>(ex: une sous-requête SELECT)"]
    C --> D["Requête principale<br>SELECT ... FROM nom_cte"]
    D --> E["Résultat final"]

    style B fill:#f9f,stroke:#333,stroke-width:1px
    style C fill:#bbf,stroke:#333,stroke-width:1px
    style D fill:#bfb,stroke:#333,stroke-width:1px

Vari­ante pour plusieurs CTE :

flowchart TD
    A["WITH cte1 AS (...),<br>cte2 AS (...)"] --> B["Requête principale<br>SELECT ... FROM cte1 JOIN cte2"]
    B --> C["Résultat final"]

    style A fill:#fdd,stroke:#333,stroke-width:1px
    style B fill:#dfd,stroke:#333,stroke-width:1px

Cas d’usage

Avant de pass­er aux exem­ples, il faut savoir qu’il existe deux sortes de CTE :

• Les CTE non récur­sives, qui per­me­t­tent de nom­mer un résul­tat tem­po­raire dans une requête, sou­vent pour sim­pli­fi­er la lec­ture ou réu­tilis­er un sous-ensem­ble de données.
• Les CTE récur­sives, qui s’appuient sur elles-mêmes pour par­courir des struc­tures hiérar­chiques, comme un arbre généalogique, un organ­i­gramme ou une arbores­cence de dossiers.

Voyons quelques cas pour chacune.

CTE récursive

Par exem­ple, pour par­courir une arbores­cence (caté­gories, organ­i­grammes, dossiers…), on peut écrire une CTE récur­sive qui évite des requêtes mul­ti­ples ou des boucles en code.

Sous-ensemble filtré

Imag­inez que vous voulez fil­tr­er des util­isa­teurs act­ifs sur une péri­ode, puis effectuer plusieurs opéra­tions dessus. La CTE vous per­met de stock­er ce résul­tat inter­mé­di­aire et de le réu­tilis­er plusieurs fois dans la même requête.

Calculs intermédiaires

Besoin d’affecter des rangs, des totaux ou autres don­nées cal­culées ? Les CTEs sont par­faites pour pré­par­er ces valeurs sans encom­br­er la requête finale.

Cas d’usage concret

Imag­i­nons que tu veuilles cal­culer, pour une entre­prise réal­isant 30 000 ventes par jour, les ventes par trimestre puis, à par­tir de ce cal­cul, faire plusieurs analy­ses : com­par­er aux trimestres précé­dents, cal­culer des parts de marché, ou appli­quer des fil­tres complexes.

Sans CTE, tu ferais prob­a­ble­ment plusieurs sous-requêtes répéti­tives, ce qui oblige le SGBD à recal­culer plusieurs fois les mêmes sommes inter­mé­di­aires. Résul­tat : perte de per­for­mance et requêtes plus lourdes.

Avec une CTE, tu écris le cal­cul une seule fois, qui est ensuite référencé plusieurs fois dans ta requête glob­ale. Le SGBD peut opti­miser et poten­tielle­ment matéri­alis­er ce résul­tat tem­po­raire, évi­tant les recalculs.

Voici ce que donne la requête avec PostgreSQL :

WITH ventes_trimestrielles AS (
    SELECT
        DATE_TRUNC('quarter', date_vente) AS trimestre,
        SUM(montant_vente) AS total_ventes
    FROM ventes
    WHERE pays = 'France' AND enseigne = 'Ikea'
    GROUP BY trimestre
)
SELECT
    trimestre,
    total_ventes,
    LAG(total_ventes) OVER (ORDER BY trimestre) AS ventes_trimestre_precedent,
    ROUND(
        100.0 * (total_ventes - COALESCE(LAG(total_ventes) OVER (ORDER BY trimestre), 0))
        / COALESCE(LAG(total_ventes) OVER (ORDER BY trimestre), 1),
        2
    ) AS evolution_pct
FROM ventes_trimestrielles
ORDER BY trimestre;

Voici la requête sans CTE :

SELECT
    trimestre,
    total_ventes,
    LAG(total_ventes) OVER (ORDER BY trimestre) AS ventes_trimestre_precedent,
    ROUND(
        100.0 * (total_ventes - COALESCE(LAG(total_ventes) OVER (ORDER BY trimestre), 0))
        / COALESCE(LAG(total_ventes) OVER (ORDER BY trimestre), 1),
        2
    ) AS evolution_pct
FROM (
    SELECT
        DATE_TRUNC('quarter', date_vente) AS trimestre,
        SUM(montant_vente) AS total_ventes
    FROM ventes
    WHERE pays = 'France' AND enseigne = 'Ikea'
    GROUP BY trimestre
) AS ventes_trimestrielles
ORDER BY trimestre;

Bénéfices observés

• Lis­i­bil­ité : le cal­cul de la somme trimestrielle est isolé et clair.
• Main­te­nance : on mod­i­fie la CTE une fois si besoin, pas plusieurs sous-requêtes.
• Per­for­mance : le moteur de base peut opti­miser le cal­cul, surtout si le vol­ume de don­nées est important.

Le béné­fice :

• Sans CTE : chaque sous-requête qui cal­cule le total doit poten­tielle­ment relire les mêmes mil­lions de lignes.
• Avec CTE matéri­al­isée : la base cal­cule la CTE une fois, stocke tem­po­raire­ment le résul­tat (qui sera bien plus petit — ici, quelques dizaines de lignes par trimestre), puis l’utilise partout.

Le gain peut être énorme :

• Temps de cal­cul divisé par 2, 5, voire 10 selon les cas
• Moins de ressources CPU et I/O utilisées
• Réduc­tion du temps d’attente côté utilisateur

En résumé

Pour un tableau de bord financier ou des analy­ses péri­odiques, les CTE sont une vraie force pour :

• Struc­tur­er pro­pre­ment le cal­cul des don­nées intermédiaires.
• Éviter les répéti­tions coû­teuses en temps machine.
• Amélior­er la lis­i­bil­ité et la main­ten­abil­ité des requêtes.

Pour une entre­prise qui traite 30 000 ventes par jour, utilis­er une CTE bien placée pour des cal­culs trimestriels, surtout si ces résul­tats sont réu­til­isés plusieurs fois dans la requête, peut dras­tique­ment amélior­er la per­for­mance et la maintenabilité.

Par­fois, le gain peut être de l’ordre de plusieurs sec­on­des à plusieurs dizaines de sec­on­des, ce qui est sig­ni­fi­catif sur un rap­port ou un tableau de bord con­sulté fréquemment.

Pour les résul­tats annuels… autant ne pas en parler.

Et pour­tant, le reproche que l’on fait le plus sou­vent aux CTE est lié aux per­for­mances. Qu’en est-il vraiment ?

Les performances des CTE

Une idée reçue veut que les CTEs dégradent les per­for­mances. Pas for­cé­ment, je dirai même : bien au contraire.

Les bases mod­ernes opti­misent sou­vent ces requêtes. Par­fois, la CTE est inlinée (comme une sous-requête clas­sique), par­fois matéri­al­isée (cal­culée une fois et stock­ée temporairement).

Il faut tester au cas par cas avec EXPLAIN pour com­pren­dre le plan d’exécution.

Limites à garder en tête

Si les Com­mon Table Expres­sions (CTE) appor­tent de la clarté et de la sou­p­lesse aux requêtes SQL, elles ne sont pas sans con­trepar­tie. Voici quelques points de vig­i­lance à con­naître avant de généralis­er leur usage.

Performances variables selon les SGBD

Post­greSQL, Db2 ou Ora­cle gèrent sou­vent les CTE de manière opti­misée, en les matéri­al­isant intel­ligem­ment pour éviter les recal­culs. À l’inverse, d’autres sys­tèmes comme MySQL (même depuis la 8.0), Ingres ou Informix pro­posent un sup­port cor­rect, mais moins affûté pour les cas com­plex­es ou les traite­ments à large échelle. L’usage de EXPLAIN est alors cru­cial pour analyser le plan d’exécution et iden­ti­fi­er les goulets d’étranglement.

CTE récursives : un outil puissant mais exigeant

Les CTE récur­sives sont idéales pour par­courir des struc­tures hiérar­chiques (caté­gories, arbres, etc.). Mais sur des vol­umes impor­tants, leur coût peut vite grimper si le moteur SQL ne gère pas effi­cace­ment les itéra­tions — c’était par exem­ple le cas sur Sybase ASE avant la ver­sion 16.

Risques de surcharge mémoire

Une CTE mal pen­sée ou appliquée sur des tables mas­sives peut devenir très gour­mande, notam­ment si elle est matéri­al­isée sans index adap­tés. Là encore, tester et sur­veiller est la clé.

Documentation inégale

Cer­tains SGBD, comme Informix ou d’anciennes ver­sions de Mari­aDB, ont adop­té les CTE tar­di­ve­ment. Résul­tat : la doc­u­men­ta­tion est par­fois lacu­naire, et l’implémentation sujette à vari­a­tions selon les versions.

En résumé

Les CTE sont pré­cieuses, mais elles méri­tent d’être util­isées avec dis­cerne­ment. Pour les requêtes cri­tiques ou à forte volumétrie, n’hésitez pas à analyser fine­ment les per­for­mances avec EXPLAIN ou son équivalent.

Environnement et gestion des CTE en entreprise

Dans un con­texte pro­fes­sion­nel où les vol­umes de don­nées sont con­séquents — comme une entre­prise qui réalise 30 000 ventes par jour — il est rare d’utiliser des bases légères comme Mari­aDB Com­mu­ni­ty. Les SGBD couram­ment employés sont Ora­cle, IBM Db2, Microsoft SQL Serv­er, Post­greSQL (ver­sion entre­prise) ou MySQL Enter­prise Edition.

Ces moteurs dis­posent d’optimisations avancées pour la ges­tion des CTE, notam­ment la capac­ité à matéri­alis­er intel­ligem­ment les résul­tats tem­po­raires afin d’éviter les recal­culs inutiles. Ils pro­posent aus­si des out­ils d’analyse appro­fondie des plans d’exécution pour s’assurer que les requêtes sont optimales.

Cela sig­ni­fie qu’en entre­prise, bien utilis­er les CTE per­met non seule­ment de struc­tur­er pro­pre­ment ses requêtes, mais aus­si d’obtenir des gains de per­for­mance sig­ni­fi­cat­ifs sur des vol­umes très importants.

En revanche, cer­taines bases moins évoluées peu­vent ne pas opti­miser les CTE, ce qui néces­site alors des tests rigoureux et par­fois des ajuste­ments spécifiques.

Vérifier le plan d’exécution d’une requête avec CTE

Pour s’assurer que la CTE est bien opti­misée et que la requête ne fait pas de recal­culs inutiles, il est essen­tiel d’examiner le plan d’exécution.

Oracle

Utilise la com­mande EXPLAIN PLAN FOR <ta requête> puis con­sulte le résul­tat avec : 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PostgreSQL

Lance :

EXPLAIN ANALYZE <ta requête>;

pour obtenir un plan détail­lé avec les temps d’exécution.

MySQL (Enterprise)

Utilise :

EXPLAIN <ta requête>;

pour voir com­ment MySQL prévoit d’exécuter la requête.

SQL Server

Dans SQL Serv­er Man­age­ment Stu­dio (SSMS), clique sur “Affich­er le plan d’exécution” avant d’exécuter la requête, ou utilise la commande : 

SET SHOWPLAN_ALL ON;
GO
<ta requête>
GO
SET SHOWPLAN_ALL OFF;
GO

Analyser ce plan per­met d’identifier si la CTE est recal­culée plusieurs fois ou si elle est matéri­al­isée une seule fois, et d’ajuster la requête ou les index en conséquence.

ORM et CTE : la dure réalité

Post­greSQL, Db2, Ingres, MySQL 8+, SQL Serv­er, Ora­cle, Informix, Mari­aDB… tous sup­por­t­ent les CTE nativement.

Mais il ne faut pas oubli­er que :

• Les CTE n’ont été définies qu’avec la norme SQL:1999, et leur adop­tion dans les SGBD a pro­gressé très lente­ment, en par­ti­c­uli­er dans les out­ils open source ou légers.
• Même des SGBD his­toriques comme IBM Db2 for i (iSeries), Informix ou Ingres n’ont inté­gré cette fonc­tion­nal­ité que très tardivement.

Et côté ORM, c’est encore une autre histoire.

Doc­trine, Hiber­nate, Elo­quent ne pren­nent pas en charge les CTEs native­ment dans leurs lan­gages de requête (DQL, HQL, Elo­quent Query Builder).

Il faut pass­er par du SQL brut ou des exten­sions, ce qui nuit à la porta­bil­ité et à la lis­i­bil­ité du code.

Résul­tat ? Si vous voulez un CTE, vous devrez prob­a­ble­ment écrire du SQL natif, au risque de per­dre un peu du con­fort de l’ORM. Ou ne pas utilis­er de CTE.

Ce n’est pas un bug. C’est juste que les ORM préfèrent mas­quer la com­plex­ité au lieu de la maîtriser. 

Enfin, la plu­part des ORM. Il y en a quand même trois qui se dis­tinguent : SQLAlche­my Core, jOOQ et Djan­go ORM.

Exploiter les CTE avec SQLAlchemy Core, jOOQ et Django ORM

Les Com­mon Table Expres­sions (CTE) sont un out­il pré­cieux pour struc­tur­er les requêtes SQL com­plex­es. Deux out­ils recon­nus — SQLAlche­my Core (Python) et jOOQ (Java) — offrent un sup­port natif et élé­gant des CTE, cha­cun avec sa pro­pre approche.

Je te donne un exem­ple en codant la même requête SQL avec cha­cun des trois ORM. On part de cette requête :

WITH recent_orders AS (
    SELECT DISTINCT user_id AS user
    FROM orders
    WHERE order_date > '2025-01-01'
)
SELECT *
FROM auth_user
WHERE id IN (SELECT user FROM recent_orders);

SQLAlchemy Core et la méthode .cte()

SQLAlche­my Core pro­pose une API flu­ide per­me­t­tant de con­stru­ire des CTE via la méth­ode .cte() sur des requêtes select(). Cette approche garde la con­struc­tion de la requête dans le code Python, sans injec­tion directe de SQL brut, facil­i­tant la com­po­si­tion dynamique de requêtes com­plex­es. Par exem­ple, pour extraire les util­isa­teurs ayant passé des com­man­des après le 1^er jan­vi­er 2025 :

from sqlalchemy import select, table, column

orders = table('orders', column('user_id'), column('order_date'))
recent_orders = select(orders.c.user_id).where(orders.c.order_date > '2025-01-01').cte('recent_orders')

users = table('users', column('id'))
query = select(users).join(recent_orders, users.c.id == recent_orders.c.user_id)

Cette requête utilise la CTE recent_orders comme une table tem­po­raire dans la requête prin­ci­pale, ren­dant le code lis­i­ble et modulaire.

jOOQ et la méthode .with()

jOOQ, quant à lui, offre une DSL Java très proche du SQL, avec un typage fort et une ges­tion intu­itive des CTE, y com­pris des CTE récur­sives. Sa méth­ode with() per­met de déclar­er claire­ment une ou plusieurs CTE avant la requête prin­ci­pale. Voici l’équivalent Java du même cas :

CommonTableExpression<Record1<Integer>> recentOrders = name("recent_orders")
    .fields("user_id")
    .as(create.select(field("user_id", Integer.class))
              .from("orders")
              .where(field("order_date").gt("2025-01-01")));

ResultQuery<?> query = create.with(recentOrders)
    .select()
    .from(table("users"))
    .join(table("recent_orders"))
    .on(field("users.id").eq(field("recent_orders.user_id")));

Grâce à cette syn­taxe, jOOQ per­met d’exploiter pleine­ment la puis­sance SQL tout en restant dans un envi­ron­nement typé et sécurisé.

Django ORM et la méthode .cte()

Depuis Djan­go 4.2 (avril 2023, ver­sion LTS), l’ORM sup­porte enfin les Com­mon Table Expres­sions (CTE) via la méth­ode .cte(), per­me­t­tant des requêtes com­plex­es sans sor­tir du cadre sécurisé de l’abstraction. Voici la même requête avec Djan­go ORM :

from django.db.models import F
from myapp.models import Order
from django.contrib.auth.models import User

# 1. CTE : commandes récentes
recent_orders_cte = (
    Order.objects
         .filter(order_date__gt='2025-01-01')
         .values('user')           # FK vers User
         .distinct()
         .cte('recent_orders')     # Nécessite Django 4.2+
)

# 2. Requête principale avec réutilisation
users_with_recent_order = (
    User.objects
        .filter(id__in=recent_orders_cte.values('user'))
)

# (facultatif) Affichage du SQL généré
print(users_with_recent_order.query)

Points impor­tants :

• QuerySet.cte(name, *, materialized=None) retourne un objet CTE (pas un QuerySet classique).
• Pour l’utiliser dans une con­di­tion, on passe cte.values('user').
• Cette syn­taxe reste lis­i­ble et 100 % portée par l’ORM ; aucune chaîne SQL brute n’est nécessaire.

En résumé, SQLAlche­my Core, jOOQ et Djan­go ORM offrent des out­ils robustes pour inté­gr­er des CTE dans vos requêtes, facil­i­tant ain­si la ges­tion de requêtes com­plex­es tout en con­ser­vant lis­i­bil­ité et main­ten­abil­ité — un atout majeur pour tout pro­jet manip­u­lant des bases rela­tion­nelles de taille significative.

Compatibilité multi-SGBD des outils SQLAlchemy Core, jOOQ et Django ORM

SQLAlche­my Core, jOOQ et Djan­go ORM sont conçus pour fonc­tion­ner avec une large gamme de sys­tèmes de ges­tion de bases de don­nées rela­tion­nelles mod­ernes : Ora­cle, Post­greSQL, MySQL/MariaDB (ver­sion 8.0+ pour le sup­port des CTE), SQL Serv­er, DB2, et bien d’autres.

Ces out­ils adaptent automa­tique­ment le SQL généré au dialecte spé­ci­fique de chaque base, ce qui garan­tit la porta­bil­ité et la cohérence des requêtes, notam­ment pour les fonc­tion­nal­ités avancées comme les CTE.

Toute­fois, il est impor­tant de soulign­er que la prise en charge effec­tive des CTE dépend avant tout du sup­port natif de la base cible. Par exem­ple, MySQL a inté­gré les CTE à par­tir de la ver­sion 8.0, tan­dis que Post­greSQL ou Ora­cle les sup­por­t­ent depuis longtemps.

Ain­si, pour exploiter pleine­ment les avan­tages des CTE via SQLAlche­my Core ou jOOQ, il con­vient de s’assurer que la base util­isée offre ce sup­port au niveau SQL.

Le prob­lème, c’est que ces deux ORM sont plus des out­siders que des chal­lengers. Pourquoi ?

En fait, plusieurs raisons expliquent pourquoi SQLAlche­my Core et jOOQ — mal­gré leur puis­sance — ne sont pas plus répan­dus pour gér­er les CTE, ou plus glob­ale­ment, dans le monde des ORM et du développe­ment courant :

1. Com­plex­ité perçue
   Ces out­ils deman­dent une cer­taine maîtrise tech­nique. SQLAlche­my Core, par exem­ple, est plus bas niveau que l’ORM clas­sique de SQLAlche­my, donc moins acces­si­ble aux devs habitués à manip­uler unique­ment des objets. jOOQ, lui, impose une approche très proche du SQL, par­fois jugée ver­beuse ou trop « tech­nique » pour cer­tains projets.
2. Habi­tudes et con­fort avec l’ORM clas­sique
   Beau­coup de devs préfèrent les ORM plus abstraits (Doc­trine, Hiber­nate, Elo­quent) parce qu’ils per­me­t­tent de manip­uler les don­nées via des objets métiers sans trop penser au SQL sous-jacent. Ces ORM n’intègrent pas les CTE, mais restent suff­isants dans une majorité de cas simples.
3. Manque de sup­port natif des CTE dans la couche ORM
   Comme on l’a dit, la plu­part des ORM clas­siques n’ont pas de sup­port des CTE en natif. Du coup, les développeurs utilisent des requêtes brutes ou évi­tent les CTE pour rester dans leur zone de confort.
4. Con­trainte du choix de la base de don­nées
   Cer­taines bases (comme MySQL avant la ver­sion 8) ne sup­por­t­aient pas les CTE, ce qui lim­i­tait leur usage et donc leur inté­gra­tion dans les out­ils. Inté­gr­er les CTE aurait imposé une réécri­t­ure au moins par­tielle de l’ORM pour con­tourn­er son absence dans MySQL.
5. Doc­u­men­ta­tion et exem­ples peu vis­i­bles
   La courbe d’apprentissage est aus­si liée à la disponi­bil­ité des ressources. SQLAlche­my Core et jOOQ ont des docs par­fois tech­niques, pas tou­jours vul­gar­isées, ce qui freine la découverte.
6. Les besoins réels
   Beau­coup de pro­jets n’ont pas besoin de requêtes ultra-com­plex­es ou récur­sives qui jus­ti­fieraient l’usage inten­sif des CTE. Le gain en clarté et per­for­mance peut être perçu comme mar­gin­al par rap­port au temps investi.

Ces lim­ites sont d’autant plus prob­lé­ma­tiques lorsqu’on souhaite exploiter les claus­es WITH (CTE) de manière fine, par exem­ple pour chaîn­er plusieurs sous-requêtes ou pour les réu­tilis­er dans une requête prin­ci­pale. Et pourtant…

CTE et ORM : Ce que vous devez savoir (Doctrine, Hibernate, Eloquent)

Les Com­mon Table Expres­sions (CTE) offrent une puis­sance remar­quable en SQL natif — pour struc­tur­er des requêtes com­plex­es, chaîn­er des sous-requêtes, ou amélior­er la lis­i­bil­ité du code.

Mais côté ORM, leur prise en charge reste iné­gale. Doc­trine, Hiber­nate ou Elo­quent per­me­t­tent par­fois d’en simuler l’usage, rarement de les exprimer pleinement.

Voici un aperçu des lim­ites actuelles… et une piste pro­pre pour exploiter les CTE si vous utilisez Doc­trine 2.8 ou supérieur.

1. Le constat : Les ORM ignorent (presque) les CTE

• Doc­trine (PHP) n’a pas de méth­ode ->with() dans son QueryBuilder.
• Pas mieux pour Hiber­nate (Java) : aucun sup­port des CTE en HQL ou Cri­te­ria API.
• Elo­quent (Lar­avel) quant à lui néces­site un pack­age tiers (staudenmeir/laravel-cte).

Pourquoi ?

Parce que les ORM priv­ilégient les sous-requêtes ou les requêtes ori­en­tées objets pour rester porta­bles et sécurisés.

2. Solution (avec précaution) : Le SQL brut

Vous pou­vez exé­cuter des CTE via des requêtes natives, mais atten­tion à deux risques majeurs :

Risque n°1 : Perte des avantages de l’ORM

• Plus de ges­tion automa­tique des rela­tions (ManyToMany, cascade, etc.)
• Plus de cache de sec­ond niveau
• Plus de pro­tec­tion automa­tique con­tre les injec­tions SQL

Risque n°2 : Injection SQL

// DANGEREUX (concaténation de chaîne)
$sql = "WITH cte AS (...) SELECT * FROM users WHERE id = " . $_GET['id'];

// SÉCURISÉ (requête paramétrée)
$sql = "WITH cte AS (...) SELECT * FROM users WHERE id = :id";
$results = $connection->executeQuery($sql, ['id' => $userInput]);

3. Alternatives recommandées (en fonction de votre besoin)

a) les sous-requêtes

Doc­trine :

$qb = $em->createQueryBuilder()
    ->select('u')
    ->from(User::class, 'u')
    ->where('u.id IN (SELECT o.user FROM App\Entity\Order o WHERE o.date > :date)');

Hiber­nate :

Subquery<Integer> subquery = query.subquery(Integer.class);
subquery.select(o.get("user").get("id"))
        .where(cb.gt(o.get("date"), date));

b) les vues SQL

Créez une vue dans votre SGBD :

CREATE VIEW recent_orders AS
SELECT user_id FROM orders WHERE date > '2025-01-01';

Puis mappez-la comme une entité (Doctrine/Hibernate).

À not­er : si tu réu­tilis­es sou­vent une agré­ga­tion sur des don­nées figées (comme c’est le cas pour des ventes trimestrielles ou annuelles), la vue est net­te­ment plus adap­tée.

La vue est idéale pour :

• Fac­toris­er une logique méti­er (cal­culs, join­tures, filtres)
• Cen­tralis­er une règle d’agrégation qui sera util­isée dans plusieurs rap­ports ou requêtes
• Amélior­er la lis­i­bil­ité des requêtes en masquant la complexité
• Te servir de base pour d’autres vues, ou même d’indicateurs dans des out­ils de Busi­ness Intel­li­gence, d’au­tant que cer­taines vues peu­vent être matéri­al­isées (MATERIALIZED VIEW) dans Post­greSQL ou Ora­cle, pour éviter de recal­culer à chaque fois.

Utilises une CTE quand :

• Tu fais une requête ponctuelle, poten­tielle­ment complexe
• Tu veux clar­i­fi­er ta requête (lis­i­bil­ité)
• Tu veux éviter de recal­culer une même sous-requête imbriquée dans une requête unique

Mais :

• La CTE n’existe que pen­dant la requête.
• Tu ne peux pas l’appeler depuis ailleurs
• Elle n’est pas fac­toris­able sur plusieurs requêtes

c) Packages dédiés

• Elo­quent : Utilisez staudenmeir/laravel-cte
• Doc­trine : Exten­sions comme beberlei/DoctrineExtensions (non officielles)

4. Quand faut-il vraiment utiliser une CTE ?

• Quand tu as besoin de récur­siv­ité (hiérar­chie parent/enfant par exemple)
• Pour l’op­ti­mi­sa­tion de requêtes com­plex­es (agré­ga­tions, dédoublonnage…)
• Dans des SGBD récents : MySQL 8+, Post­greSQL, SQL Serv­er

Que retenir ?

• Les ORM évi­tent les CTE pour des raisons de porta­bil­ité et de sécurité.
• Si vous devez les utiliser : 
  • Priv­ilégiez les requêtes paramétrées.
  • Isolez le SQL brut dans un Repos­i­to­ry ou un Ser­vice.
  • Dans 80% des cas, une sous-requête ou une vue SQL suffit.

Exemple complet (Doctrine + CTE sécurisée)

// 1. Déclarez la requête native avec paramètres
$sql = <<<SQL
WITH recent_orders AS (
    SELECT user_id FROM orders WHERE order_date > :date
)
SELECT u.* FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
SQL;

// 2. Utilisez ResultSetMapping pour convertir les résultats en entités
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata(User::class, 'u');

// 3. Exécutez de manière sécurisée
$query = $em->createNativeQuery($sql, $rsm)
    ->setParameter('date', '2025-01-01');
$users = $query->getResult();

Conclusion

Les CTEs ne sont pas une lubie de DBA bar­bu ou un gad­get compliqué.

Ce sont des out­ils puis­sants, stan­dard­is­és, lis­i­bles, et per­for­mants, qui méri­tent d’être dans votre boîte à out­ils SQL, même si votre ORM ne les intè­gre pas encore.

Alors la prochaine fois que vous devrez écrire une requête com­plexe, pensez WITH, pensez CTE. Ce n’est pas la solu­tion à tous les maux, mais elle peut en résoudre certains.

Pas­cal CESCATO

Je ne me con­tente pas de refor­muler des com­mu­niqués ou de dérouler des specs. Je teste, je creuse, je démonte quand il faut – parce qu’un sujet mal com­pris est un sujet mal écrit. J’écris avec les mains dans le cam­bouis, sans sim­pli­fi­er à out­rance ni recracher du mar­ket­ing. Mon truc, c’est de ren­dre clair sans trahir, lis­i­ble sans liss­er. Et non, je ne “fais pas du con­tenu”. Pas pour faire du con­tenu, en tout cas.