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é à Medium, on y trouve d’ex­cel­lents articles, mais hélas trop sou­vent réser­vés aux abon­nés, dont un qui relate un cas concret d’u­ti­li­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’a­bon­ne­ment… même si c’est par an, en addi­tion­nant tous les abon­ne­ments que l’on pour­rait prendre, on arrive vite à des sommes astro­no­miques. Alors je suis res­té sur ma faim.

Enfin, pas trop long­temps. D’ac­cord, je n’ai pas lu l’ar­ticle. Mais je me suis docu­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’u­sage, certes fic­tif, mais qui illustre bien les béné­fices que l’on peut en tirer.

Les Com­mon Table Expres­sions (CTEs), ou clauses WITH, sont un outil puis­sant et élé­gant pour struc­tu­rer des requêtes SQL com­plexes sans se perdre dans des sous-requêtes imbri­quées à rallonge.

Long­temps absentes de MyS­QL, elles sont pour­tant pré­sentes depuis long­temps dans des SGBD comme Post­greS­QL, SQL Ser­ver, Db2 ou Oracle. Depuis MyS­QL 8.0, elles y ont été inté­grées, mais res­tent encore sous-uti­li­sées, sou­vent igno­rées par les ORM.

Pour­tant, maî­tri­ser les CTE, c’est sim­pli­fier la lec­ture, amé­lio­rer la main­te­na­bi­li­té et plus prag­ma­ti­que­ment opti­mi­ser les per­for­mances de vos requêtes. Que vous soyez fati­gué de jon­gler avec des requêtes illi­sibles ou que vous sou­hai­tiez exploi­ter plei­ne­ment la puis­sance de votre base, les CTE méritent qu’on s’y inté­resse sérieusement.

Mais avant tout, nous allons faire…

Un peu d’histoire

L’a­dop­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 à Mathu­sa­lem – sur­tout 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 cau­che­mar pour lire, com­prendre et maintenir.

Pre­nons un exemple 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­plexi­fie, ça devient vite illi­sible. 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.

Voi­ci une repré­sen­ta­tion gra­phique (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

Variante pour plu­sieurs 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 pas­ser aux exemples, il faut savoir qu’il existe deux sortes de CTE :

• Les CTE non récur­sives, qui per­mettent de nom­mer un résul­tat tem­po­raire dans une requête, sou­vent pour sim­pli­fier la lec­ture ou réuti­li­ser un sous-ensemble de données.
• Les CTE récur­sives, qui s’appuient sur elles-mêmes pour par­cou­rir des struc­tures hié­rar­chiques, comme un arbre généa­lo­gique, un orga­ni­gramme ou une arbo­res­cence de dossiers.

Voyons quelques cas pour chacune.

CTE récursive

Par exemple, pour par­cou­rir une arbo­res­cence (caté­go­ries, orga­ni­grammes, dos­siers…), on peut écrire une CTE récur­sive qui évite des requêtes mul­tiples ou des boucles en code.

Sous-ensemble filtré

Ima­gi­nez que vous vou­lez fil­trer des uti­li­sa­teurs actifs sur une période, puis effec­tuer plu­sieurs opé­ra­tions des­sus. La CTE vous per­met de sto­cker ce résul­tat inter­mé­diaire et de le réuti­li­ser plu­sieurs fois dans la même requête.

Calculs intermédiaires

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

Cas d’usage concret

Ima­gi­nons que tu veuilles cal­cu­ler, pour une entre­prise réa­li­sant 30 000 ventes par jour, les ventes par tri­mestre puis, à par­tir de ce cal­cul, faire plu­sieurs ana­lyses : com­pa­rer aux tri­mestres pré­cé­dents, cal­cu­ler des parts de mar­ché, ou appli­quer des filtres complexes.

Sans CTE, tu ferais pro­ba­ble­ment plu­sieurs sous-requêtes répé­ti­tives, ce qui oblige le SGBD à recal­cu­ler plu­sieurs fois les mêmes sommes inter­mé­diaires. 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é­ren­cé plu­sieurs fois dans ta requête glo­bale. Le SGBD peut opti­mi­ser et poten­tiel­le­ment maté­ria­li­ser ce résul­tat tem­po­raire, évi­tant les recalculs.

Voi­ci 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;

Voi­ci 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

• Lisi­bi­li­té : le cal­cul de la somme tri­mes­trielle est iso­lé et clair.
• Main­te­nance : on modi­fie la CTE une fois si besoin, pas plu­sieurs sous-requêtes.
• Per­for­mance : le moteur de base peut opti­mi­ser le cal­cul, sur­tout si le volume de don­nées est important.

Le béné­fice :

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

Le gain peut être énorme :

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

En résumé

Pour un tableau de bord finan­cier ou des ana­lyses pério­diques, les CTE sont une vraie force pour :

• Struc­tu­rer pro­pre­ment le cal­cul des don­nées intermédiaires.
• Évi­ter les répé­ti­tions coû­teuses en temps machine.
• Amé­lio­rer la lisi­bi­li­té et la main­te­na­bi­li­té des requêtes.

Pour une entre­prise qui traite 30 000 ventes par jour, uti­li­ser une CTE bien pla­cée pour des cal­culs tri­mes­triels, sur­tout si ces résul­tats sont réuti­li­sés plu­sieurs fois dans la requête, peut dras­ti­que­ment amé­lio­rer la per­for­mance et la maintenabilité.

Par­fois, le gain peut être de l’ordre de plu­sieurs secondes à plu­sieurs dizaines de secondes, ce qui est signi­fi­ca­tif sur un rap­port ou un tableau de bord consul­té 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 modernes opti­misent sou­vent ces requêtes. Par­fois, la CTE est inli­née (comme une sous-requête clas­sique), par­fois maté­ria­li­sée (cal­cu­lée une fois et sto­ckée temporairement).

Il faut tes­ter au cas par cas avec EXPLAIN pour com­prendre le plan d’exécution.

Limites à garder en tête

Si les Com­mon Table Expres­sions (CTE) apportent de la clar­té et de la sou­plesse aux requêtes SQL, elles ne sont pas sans contre­par­tie. Voi­ci quelques points de vigi­lance à connaître avant de géné­ra­li­ser leur usage.

Performances variables selon les SGBD

Post­greS­QL, Db2 ou Oracle gèrent sou­vent les CTE de manière opti­mi­sée, en les maté­ria­li­sant intel­li­gem­ment pour évi­ter les recal­culs. À l’inverse, d’autres sys­tèmes comme MyS­QL (même depuis la 8.0), Ingres ou Infor­mix pro­posent un sup­port cor­rect, mais moins affû­té pour les cas com­plexes ou les trai­te­ments à large échelle. L’usage de EXPLAIN est alors cru­cial pour ana­ly­ser le plan d’exécution et iden­ti­fier les gou­lets d’étranglement.

CTE récursives : un outil puissant mais exigeant

Les CTE récur­sives sont idéales pour par­cou­rir des struc­tures hié­rar­chiques (caté­go­ries, arbres, etc.). Mais sur des volumes impor­tants, leur coût peut vite grim­per si le moteur SQL ne gère pas effi­ca­ce­ment les ité­ra­tions — c’était par exemple le cas sur Sybase ASE avant la ver­sion 16.

Risques de surcharge mémoire

Une CTE mal pen­sée ou appli­quée sur des tables mas­sives peut deve­nir très gour­mande, notam­ment si elle est maté­ria­li­sée sans index adap­tés. Là encore, tes­ter et sur­veiller est la clé.

Documentation inégale

Cer­tains SGBD, comme Infor­mix ou d’anciennes ver­sions de MariaDB, ont adop­té les CTE tar­di­ve­ment. Résul­tat : la docu­men­ta­tion est par­fois lacu­naire, et l’implémentation sujette à varia­tions selon les versions.

En résumé

Les CTE sont pré­cieuses, mais elles méritent d’être uti­li­sées avec dis­cer­ne­ment. Pour les requêtes cri­tiques ou à forte volu­mé­trie, n’hésitez pas à ana­ly­ser fine­ment les per­for­mances avec EXPLAIN ou son équivalent.

Environnement et gestion des CTE en entreprise

Dans un contexte pro­fes­sion­nel où les volumes de don­nées sont consé­quents — comme une entre­prise qui réa­lise 30 000 ventes par jour — il est rare d’utiliser des bases légères comme MariaDB Com­mu­ni­ty. Les SGBD cou­ram­ment employés sont Oracle, IBM Db2, Micro­soft SQL Ser­ver, Post­greS­QL (ver­sion entre­prise) ou MyS­QL Enter­prise Edition.

Ces moteurs dis­posent d’optimisations avan­cées pour la ges­tion des CTE, notam­ment la capa­ci­té à maté­ria­li­ser intel­li­gem­ment les résul­tats tem­po­raires afin d’éviter les recal­culs inutiles. Ils pro­posent aus­si des outils d’analyse appro­fon­die des plans d’exécution pour s’assurer que les requêtes sont optimales.

Cela signi­fie qu’en entre­prise, bien uti­li­ser les CTE per­met non seule­ment de struc­tu­rer pro­pre­ment ses requêtes, mais aus­si d’obtenir des gains de per­for­mance signi­fi­ca­tifs sur des volumes très importants.

En revanche, cer­taines bases moins évo­luées peuvent ne pas opti­mi­ser les CTE, ce qui néces­site alors des tests rigou­reux et par­fois des ajus­te­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­mi­sé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

Uti­lise la com­mande EXPLAIN PLAN FOR <ta requête> puis consulte le résul­tat avec : 

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

PostgreSQL

Lance :

EXPLAIN ANALYZE <ta requête>;

pour obte­nir un plan détaillé avec les temps d’exécution.

MySQL (Enterprise)

Uti­lise :

EXPLAIN <ta requête>;

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

SQL Server

Dans SQL Ser­ver Mana­ge­ment Stu­dio (SSMS), clique sur “Affi­cher le plan d’exécution” avant d’exécuter la requête, ou uti­lise la commande : 

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

Ana­ly­ser ce plan per­met d’identifier si la CTE est recal­cu­lée plu­sieurs fois ou si elle est maté­ria­li­sée une seule fois, et d’ajuster la requête ou les index en conséquence.

ORM et CTE : la dure réalité

Post­greS­QL, Db2, Ingres, MyS­QL 8+, SQL Ser­ver, Oracle, Infor­mix, MariaDB… tous sup­portent les CTE nativement.

Mais il ne faut pas oublier que :

• Les CTE n’ont été défi­nies qu’a­vec la norme SQL:1999, et leur adop­tion dans les SGBD a pro­gres­sé très len­te­ment, en par­ti­cu­lier dans les outils open source ou légers.
• Même des SGBD his­to­riques comme IBM Db2 for i (iSe­ries), Infor­mix ou Ingres n’ont inté­gré cette fonc­tion­na­li­té que très tardivement.

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

Doc­trine, Hiber­nate, Eloquent ne prennent pas en charge les CTEs nati­ve­ment dans leurs lan­gages de requête (DQL, HQL, Eloquent Que­ry Builder).

Il faut pas­ser par du SQL brut ou des exten­sions, ce qui nuit à la por­ta­bi­li­té et à la lisi­bi­li­té du code.

Résul­tat ? Si vous vou­lez un CTE, vous devrez pro­ba­ble­ment écrire du SQL natif, au risque de perdre un peu du confort de l’ORM. Ou ne pas uti­li­ser de CTE.

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

Enfin, la plu­part des ORM. Il y en a quand même trois qui se dis­tinguent : SQLAl­che­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 outil pré­cieux pour struc­tu­rer les requêtes SQL com­plexes. Deux outils recon­nus — SQLAl­che­my Core (Python) et jOOQ (Java) — offrent un sup­port natif et élé­gant des CTE, cha­cun avec sa propre approche.

Je te donne un exemple 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()

SQLAl­che­my Core pro­pose une API fluide per­met­tant de construire des CTE via la méthode .cte() sur des requêtes select(). Cette approche garde la construc­tion de la requête dans le code Python, sans injec­tion directe de SQL brut, faci­li­tant la com­po­si­tion dyna­mique de requêtes com­plexes. Par exemple, pour extraire les uti­li­sa­teurs ayant pas­sé des com­mandes après le 1er jan­vier 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 uti­lise la CTE recent_orders comme une table tem­po­raire dans la requête prin­ci­pale, ren­dant le code lisible 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 intui­tive des CTE, y com­pris des CTE récur­sives. Sa méthode with() per­met de décla­rer clai­re­ment une ou plu­sieurs CTE avant la requête prin­ci­pale. Voi­ci 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 plei­ne­ment la puis­sance SQL tout en res­tant dans un envi­ron­ne­ment typé et sécu­ri­sé.

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éthode .cte(), per­met­tant des requêtes com­plexes sans sor­tir du cadre sécu­ri­sé de l’abstraction. Voi­ci 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 condi­tion, on passe cte.values('user').
• Cette syn­taxe reste lisible et 100 % por­tée par l’ORM ; aucune chaîne SQL brute n’est nécessaire.

En résu­mé, SQLAl­che­my Core, jOOQ et Djan­go ORM offrent des outils robustes pour inté­grer des CTE dans vos requêtes, faci­li­tant ain­si la ges­tion de requêtes com­plexes tout en conser­vant lisi­bi­li­té et main­te­na­bi­li­té — un atout majeur pour tout pro­jet mani­pu­lant des bases rela­tion­nelles de taille significative.

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

SQLAl­che­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 modernes : Oracle, Post­greS­QL, MySQL/MariaDB (ver­sion 8.0+ pour le sup­port des CTE), SQL Ser­ver, DB2, et bien d’autres.

Ces outils adaptent auto­ma­ti­que­ment le SQL géné­ré au dia­lecte spé­ci­fique de chaque base, ce qui garan­tit la por­ta­bi­li­té et la cohé­rence des requêtes, notam­ment pour les fonc­tion­na­li­tés avan­cées comme les CTE.

Tou­te­fois, il est impor­tant de sou­li­gner que la prise en charge effec­tive des CTE dépend avant tout du sup­port natif de la base cible. Par exemple, MyS­QL a inté­gré les CTE à par­tir de la ver­sion 8.0, tan­dis que Post­greS­QL ou Oracle les sup­portent depuis longtemps.

Ain­si, pour exploi­ter plei­ne­ment les avan­tages des CTE via SQLAl­che­my Core ou jOOQ, il convient de s’assurer que la base uti­li­sée offre ce sup­port au niveau SQL.

Le pro­blème, c’est que ces deux ORM sont plus des out­si­ders que des chal­len­gers. Pourquoi ?

En fait, plu­sieurs rai­sons expliquent pour­quoi SQLAl­che­my Core et jOOQ — mal­gré leur puis­sance — ne sont pas plus répan­dus pour gérer les CTE, ou plus glo­ba­le­ment, dans le monde des ORM et du déve­lop­pe­ment courant :

1. Com­plexi­té per­çue
   Ces outils demandent une cer­taine maî­trise tech­nique. SQLAl­che­my Core, par exemple, est plus bas niveau que l’ORM clas­sique de SQLAl­che­my, donc moins acces­sible aux devs habi­tués à mani­pu­ler uni­que­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 confort avec l’ORM clas­sique
   Beau­coup de devs pré­fèrent les ORM plus abs­traits (Doc­trine, Hiber­nate, Eloquent) parce qu’ils per­mettent de mani­pu­ler les don­nées via des objets métiers sans trop pen­ser au SQL sous-jacent. Ces ORM n’intègrent pas les CTE, mais res­tent suf­fi­sants dans une majo­ri­té 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éve­lop­peurs uti­lisent des requêtes brutes ou évitent les CTE pour res­ter dans leur zone de confort.
4. Contrainte du choix de la base de don­nées
   Cer­taines bases (comme MyS­QL avant la ver­sion 8) ne sup­por­taient pas les CTE, ce qui limi­tait leur usage et donc leur inté­gra­tion dans les outils. Inté­grer les CTE aurait impo­sé une réécri­ture au moins par­tielle de l’ORM pour contour­ner son absence dans MySQL.
5. Docu­men­ta­tion et exemples peu visibles
   La courbe d’apprentissage est aus­si liée à la dis­po­ni­bi­li­té des res­sources. SQLAl­che­my Core et jOOQ ont des docs par­fois tech­niques, pas tou­jours vul­ga­ri­sé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­plexes ou récur­sives qui jus­ti­fie­raient l’usage inten­sif des CTE. Le gain en clar­té et per­for­mance peut être per­çu comme mar­gi­nal par rap­port au temps investi.

Ces limites sont d’autant plus pro­blé­ma­tiques lorsqu’on sou­haite exploi­ter les clauses WITH (CTE) de manière fine, par exemple pour chaî­ner plu­sieurs sous-requêtes ou pour les réuti­li­ser 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­tu­rer des requêtes com­plexes, chaî­ner des sous-requêtes, ou amé­lio­rer la lisi­bi­li­té du code.

Mais côté ORM, leur prise en charge reste inégale. Doc­trine, Hiber­nate ou Eloquent per­mettent par­fois d’en simu­ler l’usage, rare­ment de les expri­mer pleinement.

Voi­ci un aper­çu des limites actuelles… et une piste propre pour exploi­ter les CTE si vous uti­li­sez Doc­trine 2.8 ou supé­rieur.

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

• Doc­trine (PHP) n’a pas de méthode ->with() dans son QueryBuilder.
• Pas mieux pour Hiber­nate (Java) : aucun sup­port des CTE en HQL ou Cri­te­ria API.
• Eloquent (Lara­vel) quant à lui néces­site un package tiers (staudenmeir/laravel-cte).

Pour­quoi ?

Parce que les ORM pri­vi­lé­gient les sous-requêtes ou les requêtes orien­tées objets pour res­ter por­tables et sécurisés.

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

Vous pou­vez exé­cu­ter 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 auto­ma­tique des rela­tions (ManyToMany, cascade, etc.)
• Plus de cache de second niveau
• Plus de pro­tec­tion auto­ma­tique contre 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 map­pez-la comme une enti­té (Doctrine/Hibernate).

À noter : si tu réuti­lises sou­vent une agré­ga­tion sur des don­nées figées (comme c’est le cas pour des ventes tri­mes­trielles ou annuelles), la vue est net­te­ment plus adap­tée.

La vue est idéale pour :

• Fac­to­ri­ser une logique métier (cal­culs, join­tures, filtres)
• Cen­tra­li­ser une règle d’agrégation qui sera uti­li­sée dans plu­sieurs rap­ports ou requêtes
• Amé­lio­rer la lisi­bi­li­té des requêtes en mas­quant la complexité
• Te ser­vir de base pour d’autres vues, ou même d’indicateurs dans des outils de Busi­ness Intel­li­gence, d’au­tant que cer­taines vues peuvent être maté­ria­li­sées (MATERIALIZED VIEW) dans Post­greS­QL ou Oracle, pour évi­ter de recal­cu­ler à chaque fois.

Utilises une CTE quand :

• Tu fais une requête ponc­tuelle, poten­tiel­le­ment complexe
• Tu veux cla­ri­fier ta requête (lisi­bi­li­té)
• Tu veux évi­ter de recal­cu­ler une même sous-requête imbri­qué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­to­ri­sable sur plu­sieurs requêtes

c) Packages dédiés

• Eloquent : Uti­li­sez 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­si­vi­té (hié­rar­chie parent/enfant par exemple)
• Pour l’op­ti­mi­sa­tion de requêtes com­plexes (agré­ga­tions, dédoublonnage…)
• Dans des SGBD récents : MyS­QL 8+, Post­greS­QL, SQL Ser­ver

Que retenir ?

• Les ORM évitent les CTE pour des rai­sons de por­ta­bi­li­té et de sécurité.
• Si vous devez les utiliser : 
  • Pri­vi­lé­giez les requêtes para­mé­trées.
  • Iso­lez le SQL brut dans un Repo­si­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 outils puis­sants, stan­dar­di­sés, lisibles, et per­for­mants, qui méritent d’être dans votre boîte à outils SQL, même si votre ORM ne les intègre pas encore.

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

Pas­cal CESCATO

Je ne me contente pas de refor­mu­ler des com­mu­ni­qués ou de dérou­ler 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­fier à outrance ni recra­cher du mar­ke­ting. Mon truc, c’est de rendre clair sans tra­hir, lisible sans lis­ser. Et non, je ne “fais pas du conte­nu”. Pas pour faire du conte­nu, en tout cas.