Esempio di CTE ricorsivi per lavorare con le gerarchie

Immagina: hai un e-commerce con migliaia di prodotti, tutti sistemati per bene sugli scaffali — categorie, sottocategorie, sotto-sottocategorie. Sul sito sembra un bel menu a tendina, ma nel database diventa un bel casino. Come fai a tirare fuori tutto il ramo "Elettronica → Smartphone → Accessori" con una sola query? Come conti quanti livelli di annidamento ha ogni categoria? I soliti JOIN qui non bastano — serve la ricorsione!

Costruire la struttura delle categorie dei prodotti con i CTE ricorsivi

Uno dei problemi classici nei database relazionali è lavorare con strutture gerarchiche. Immagina di avere un albero di categorie prodotto: categorie principali, sottocategorie, sotto-sottocategorie e così via. Per esempio:

Elettronica
  └── Smartphone
      └── Accessori
  └── Notebook
      └── Gaming
  └── Foto e video

Questa struttura è facile da visualizzare nelle interfacce degli e-commerce, ma come la salvi nel database e la tiri fuori? Qui entrano in gioco i CTE ricorsivi!

Tabella di partenza delle categorie

Per prima cosa creiamo la tabella categories, che conterrà i dati delle categorie prodotto:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Identificatore unico della categoria
    category_name TEXT NOT NULL,          -- Nome della categoria
    parent_category_id INT                -- Categoria genitore (NULL per le categorie principali)
);

Ecco un esempio di dati che inseriamo nella tabella:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Elettronica', NULL),
    ('Smartphone', 1),
    ('Accessori', 2),
    ('Notebook', 1),
    ('Gaming', 4),
    ('Foto e video', 1);

Cosa succede qui:

• Elettronica — è la categoria principale (nessun genitore, parent_category_id = NULL).
• Smartphone sta dentro la categoria Elettronica.
• Accessori appartiene alla categoria Smartphone.
• Stesso discorso per le altre categorie.

La struttura attuale dei dati nella tabella categories è questa:

Costruire l’albero delle categorie con un CTE ricorsivo

Ora vogliamo ottenere tutta la gerarchia delle categorie con il livello di annidamento. Per farlo usiamo un CTE ricorsivo.

WITH RECURSIVE category_tree AS (
    -- Query base: selezioniamo tutte le categorie radice (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Primo livello di annidamento
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Query ricorsiva: troviamo le sottocategorie per ogni categoria
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Aumentiamo il livello di annidamento
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Query finale: estraiamo i risultati dal CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Risultato:

Cosa succede qui?

1. Prima la query base (SELECT … FROM categories WHERE parent_category_id IS NULL) prende le categorie principali. In questo caso solo Elettronica con depth = 1.
2. Poi la query ricorsiva con INNER JOIN aggiunge le sottocategorie, aumentando il livello (depth + 1).
3. Questo processo si ripete finché non trova tutte le sottocategorie a tutti i livelli.

Modifiche utili

L’esempio base funziona, ma nei progetti veri spesso serve di più. Metti che vuoi fare il breadcrumb per il sito o mostrare al manager in quale categoria ci sono più sottosezioni. Vediamo qualche miglioramento pratico della nostra query.

1. Aggiungere il percorso completo della categoria

A volte è utile mostrare il percorso completo della categoria, tipo: Elettronica > Smartphone > Accessori. Si può fare con l’aggregazione delle stringhe:

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, -- concateniamo le stringhe
        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;

Risultato:

Ora ogni categoria ha il percorso completo che mostra l’annidamento.

2. Contare il numero di sottocategorie

Metti che vuoi sapere quante sottocategorie ha ogni categoria?

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;

Risultato:

La tabella mostra che Elettronica ha 3 sottocategorie (Smartphone, Notebook, Foto e video), mentre Smartphone e Notebook ne hanno una ciascuno.

Particolarità ed errori tipici con i CTE ricorsivi

Ricorsione infinita: Se i dati hanno dei cicli (tipo una categoria che punta a se stessa), la query può andare in loop infinito. Per evitarlo, puoi limitare la profondità con WHERE depth < N o con limiti.

Ottimizzazione: I CTE ricorsivi possono essere lenti su grandi moli di dati. Metti un indice su parent_category_id per velocizzare.

Errore UNION invece di UNION ALL: Usa sempre UNION ALL nei CTE ricorsivi, altrimenti PostgreSQL cerca di togliere i duplicati e rallenta tutto.

Questo esempio mostra come i CTE ricorsivi ti aiutano a lavorare con le strutture gerarchiche. Saper estrarre gerarchie dal database ti servirà in un sacco di progetti veri. Tipo per costruire i menu del sito, analizzare strutture organizzative o lavorare con i grafi. Ora sei pronto per affrontare problemi di qualsiasi livello.

1

Sondaggio/quiz

Introduzione ai CTE, livello 27, lezione 4

Non disponibile

Introduzione ai CTE

Introduzione ai CTE

Apri