Exemple de CTE récursifs pour bosser avec des hiérarchies

Imagine : t’as une boutique en ligne avec des milliers de produits, bien rangés sur des étagères — catégories, sous-catégories, sous-sous-catégories. Sur le site, ça fait un joli menu déroulant, mais dans la base de données, c’est vite le casse-tête. Comment récupérer toute la branche "Électronique → Smartphones → Accessoires" en une seule requête ? Comment compter le nombre de niveaux d’imbrication pour chaque catégorie ? Les JOIN classiques ne suffisent pas — là, il te faut de la récursivité !

Construire la structure des catégories de produits avec des CTE récursifs

Un des grands classiques dans les bases de données relationnelles, c’est de gérer des structures hiérarchiques. Imagine que t’as un arbre de catégories de produits : catégories principales, sous-catégories, sous-sous-catégories, etc. Par exemple :

Électronique
  └── Smartphones
      └── Accessoires
  └── Ordinateurs portables
      └── Gaming
  └── Photo et vidéo

Cette structure est facile à afficher dans l’interface d’une boutique en ligne, mais comment la stocker et l’extraire de la base de données ? C’est là que les CTE récursifs entrent en jeu !

Table de base des catégories

D’abord, on va créer une table categories pour stocker les infos sur les catégories de produits :

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Identifiant unique de la catégorie
    category_name TEXT NOT NULL,          -- Nom de la catégorie
    parent_category_id INT                -- Catégorie parente (NULL pour les catégories principales)
);

Voici un exemple de données qu’on va insérer dans la table :

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Électronique', NULL),
    ('Smartphones', 1),
    ('Accessoires', 2),
    ('Ordinateurs portables', 1),
    ('Gaming', 4),
    ('Photo et vidéo', 1);

Ce qui se passe ici :

• Électronique — c’est la catégorie principale (pas de parent, parent_category_id = NULL).
• Smartphones est dans la catégorie Électronique.
• Accessoires dépend de la catégorie Smartphones.
• Pareil pour les autres catégories.

La structure actuelle des données dans la table categories ressemble à ça :

Construire l’arbre des catégories avec un CTE récursif

Maintenant, on veut récupérer toute la hiérarchie des catégories avec le niveau d’imbrication. Pour ça, on utilise un CTE récursif.

WITH RECURSIVE category_tree AS (
    -- Requête de base : on prend toutes les catégories racines (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Premier niveau d’imbrication
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Requête récursive : on trouve les sous-catégories pour chaque catégorie
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- On augmente le niveau d’imbrication
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Requête finale : on extrait les résultats du CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Résultat :

Qu’est-ce qui se passe ici ?

1. D’abord, la requête de base (SELECT … FROM categories WHERE parent_category_id IS NULL) prend les catégories principales. Ici, c’est juste Électronique avec depth = 1.
2. Puis la requête récursive avec INNER JOIN ajoute les sous-catégories, en augmentant le niveau (depth + 1).
3. Ce processus continue jusqu’à ce qu’on ait trouvé toutes les sous-catégories à tous les niveaux.

Améliorations utiles

L’exemple de base marche, mais dans les vrais projets, on a souvent besoin de plus. Par exemple, tu veux faire du breadcrumb pour le site ou montrer au manager dans quelle catégorie il y a le plus de sous-catégories. Voyons quelques améliorations pratiques de notre requête.

1. Ajouter le chemin complet de la catégorie

Parfois, c’est pratique d’afficher le chemin complet d’une catégorie, genre : Électronique > Smartphones > Accessoires. On peut faire ça avec de l’agrégation de chaînes :

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- on concatène les chaînes
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Résultat :

Maintenant, chaque catégorie a son chemin complet qui montre l’imbrication.

2. Compter le nombre de sous-catégories

Et si tu veux savoir combien de sous-catégories il y a pour chaque catégorie ?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

Résultat :

Le tableau montre que Électronique a 3 sous-catégories (Smartphones, Ordinateurs portables, Photo et vidéo), et Smartphones et Ordinateurs portables en ont chacun une.

Particularités et erreurs fréquentes avec les CTE récursifs

Récursivité infinie : Si tes données ont des cycles (genre une catégorie qui pointe sur elle-même), la requête peut partir en boucle infinie. Pour éviter ça, tu peux limiter la profondeur avec WHERE depth < N ou des limites.

Optimisation : Les CTE récursifs peuvent ramer sur de gros volumes de données. Mets des index sur parent_category_id pour accélérer.

Erreur UNION au lieu de UNION ALL : Utilise toujours UNION ALL pour les CTE récursifs, sinon PostgreSQL va essayer de virer les doublons, ce qui ralentit la requête.

Cet exemple montre comment les CTE récursifs t’aident à gérer des structures hiérarchiques. Savoir extraire des hiérarchies d’une base de données, ça te servira dans plein de projets réels : menus de site, analyse de structures d’entreprise, ou même bosser avec des graphes. Maintenant, t’es prêt à attaquer des problèmes de n’importe quelle complexité.

1

Étude/Quiz

Introduction aux CTE, niveau 27, leçon 4

Indisponible

Introduction aux CTE

Introduction aux CTE

Ouvrir