Formation Apprendre et maitriser SQL : Requête récursive

Dans ce chapitre nous allons voir comment écrire des requêtes pour récupérer des données récursives (recursive common table expressions).

• SQLite
• MySQL
• PostgreSQL

Le mot clef WITH permet d'écrire des déclarations auixiliaires que l'on pourra utiliser dans une requête plus large. Ces déclarations, souvent appelé Common Table Expression ou CTE, peuvent être vu comme des tables temporaires qui n'existe que pour une requête.

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Il est possible d'utiliser WITH afin de récupérer des données de manière récursive. Dans ce cas là la déclaration dans le AS sera décomposé en 2 déclarations regroupé par un UNION (ou UNION ALL).

• Une première déclaration récupèrera les données à la racine de notre récursion.
• Une seconde déclaration qui contiendra une référence au résultat de la requête précédente.

Pour l'exemple nous allons récupérer les catégories parentes récursivement.

WITH RECURSIVE categories_tree AS (
    SELECT id, name, parent_id FROM categories WHERE id = 14 /* On récupère la catégorie en profondeur */
    UNION ALL
    SELECT c.id, c.name, c.parent_id FROM categories c, categories_tree WHERE c.id = categories_tree.parent_id
)
SELECT * FROM categories_tree

Il est aussi possible de calculer la profondeur et le chemin vers une catégorie si on commence par la racine.

WITH RECURSIVE children (id, name, parent_id, level, path) AS (
    SELECT id, name, parent_id, 0, name FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        c.id,
        c.name, 
        c.parent_id, 
        children.level + 1,
        children.path || " > " || c.name
    FROM categories c, children
    WHERE c.parent_id = children.id
)
SELECT * FROM children

Pour tester

Si vous souhaitez tester ce types de requête voici une structure que vous pouvez utiliser.

CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

INSERT INTO categories
VALUES (1, 'Mammifère', NULL),
       (2, 'Chien', 1),
       (3, 'Chat', 1),
       (4, 'Singe', 1),
       (5, 'Gorille', 4),
       (6, 'Chimpanzé', 4),
       (7, 'Shiba', 2),
       (8, 'Corgi', 2),
       (9, 'Labrador', 2),
       (10, 'Poisson', NULL),
       (11, 'Requin', 10),
       (12, 'Requin blanc', 11),
       (13, 'Grand requin blanc', 12),
       (14, 'Petit requin blanc', 12),
       (15, 'Requin marteau', 11),
       (16, 'Requin tigre', 11),
       (17, 'Poisson rouge', 10),
       (18, 'Poisson chat', 10);