Creare query ricorsive

Cosa sono e a cosa servono le query ricorsive

Le query ricorsive sono query che richiamano se stesse. Un esempio abbastanza frequente si ha quando una tabella contiene una struttura implicita ad albero, con un campo ID parent che punta all’ID della tabella stessa

Esempio:

Che può essere rappresentata graficamente in questo modo:

Da una tabella di questo genere è possibile ottenere, tramite query ricorsive, informazioni sul percorso e la radice, e ottenere un risultato simile a:

Come si crea una query ricorsiva

Dati di esempio

Possiamo usare una variabile tabella per memorizzare i dati del nostro test:

declare @p table (ID int, Elemento varchar(200), ID_Parent int)
insert into @p values
(1, 'Terra', NULL),
(2, 'Italia', 1),
(3, 'Veneto', 2),
(4, 'Lombardia', 2),
(5, 'Venezia', 3);

N.B. il punto e virgola finale è necessario per poter dichiarare la variabile tabella prima della query ricorsiva.

La query ricorsiva

La query finale è la seguente, di seguito ne commenteremo ogni parte:

WITH Ric (ID, Elemento, ID_Parent, Percorso, Radice)
AS (
    SELECT ID, Elemento, ID_Parent,
    cast('/Terra' as varchar(max)) as Percorso,
    cast('Terra' as varchar(max)) as Radice
    FROM @p
    WHERE ID_Parent IS NULL
    UNION ALL
    SELECT p.ID, p.Elemento, p.ID_Parent,
    Ric.Percorso + '/' + p.Elemento as Percorso, Ric.Radice
    FROM @p AS p INNER JOIN Ric on p.ID_Parent = Ric.ID
)
SELECT ID, Elemento, Percorso, Radice
FROM Ric

Per creare una query ricorsiva occorre distinguere tra la query che restituisce i dati “radice” e la parte ricorsiva. Nel nostro esempio, la prima riga è l’elemento radice, e si distingue dal fatto che ha ID_Parent NULL.

SELECT ID, Elemento, ID_Parent
FROM @p
WHERE ID_Parent IS NULL

Oltre a questo, dobbiamo predisporre due campi che facciano da “segnaposto” per il percorso e la radice

WITH Ric (ID, Elemento, ID_Parent, Percorso, Radice)
AS (
    SELECT ID, Elemento, ID_Parent,
    cast('/Terra' as varchar(max)) as Percorso,
    cast('Terra' as varchar(max)) as Radice
    FROM @p
    WHERE ID_Parent IS NULL
    UNION ALL
    SELECT p.ID, p.Elemento, p.ID_Parent,
    Ric.Percorso + '/' + p.Elemento as Percorso, Ric.Radice
    FROM @p AS p INNER JOIN Ric on p.ID_Parent = Ric.ID
)
SELECT ID, Elemento, Percorso, Radice
FROM Ric

Un’accortezza: i campi Percorso e Radice dovranno contenere una quantità di testo che non è possibile determinare a priori, perché dipendono dal calcolo della query stessa, per questo devono essere convertiti in testi con la lunghezza massima possibile. Se i campi ricorsivi non fossero di testo, sarebbe comunque necessario prevedere lunghezze arbitrarie.

A questa prima query aggiungiamo con UNION ALL (attenzione a non usare solo UNION!) anche la query che restituisce tutti gli altri risultati (in grassetto):

WITH Ric (ID, Elemento, ID_Parent, Percorso, Radice)
AS (
    SELECT ID, Elemento, ID_Parent,
        cast('/Terra' as varchar(max)) as Percorso,
        cast('Terra' as varchar(max)) as Radice
    FROM Pianeta
    WHERE ID_Parent IS NULL
    UNION ALL
    SELECT p.ID, p.Elemento, p.ID_Parent,
        Ric.Percorso + '/' + p.Elemento as Percorso, Ric.Radice
    FROM Pianeta AS p INNER JOIN Ric on p.ID_Parent = Ric.ID
)
SELECT ID, Elemento, Percorso, Radice
FROM Ric

La seconda parte della UNION ALL utilizza il risultato parziale della query, che nell’esempio si chiama ric, per fare una join con la tabella @p

SELECT p.ID, p.Elemento, p.ID_Parent
Ric.Percorso + '/' + p.Elemento as Percorso, Ric.Radice
FROM @p AS p INNER JOIN Ric on p.ID_Parent = Ric.ID

In pratica, Ric si comporta come se fosse una tabella che contiene di volta in volta l’elemento genitore di quello corrente.

Il comportamento di Ric si intuisce dal risultato della concatenazione Ric.Percorso + ‘/’ + p.Elemento: Ric.Percorso è la parte di percorso calcolata fino a quel momento (es. /Terra/Italia) mentre p.Elemento è l’elemento corrente (es. Veneto).

La parola chiave WITH dichiara la “tabella” fittizia Ric, definendo i nomi dei campi. I tipi dei campi sono ricavati dalla prima query, quella in cui l’elemento genitore è NULL. Questo è il motivo per cui si dichiarano i campi come varchar(max) all’interno della prima parte della UNION ALL.

WITH Ric (ID, Elemento, ID_Parent, Percorso, Radice)
AS (
    SELECT ID, Elemento, ID_Parent,
    cast('/Terra' as varchar(max)) as Percorso,
    cast('Terra' as varchar(max)) as Radice
    FROM Pianeta
    WHERE ID_Parent IS NULL
    UNION ALL
    SELECT p.ID, p.Elemento, p.ID_Parent,
    Ric.Percorso + '/' + p.Elemento as Percorso, Ric.Radice
    FROM @p AS p INNER JOIN Ric on p.ID_Parent = Ric.ID
)
SELECT ID, Elemento, Percorso, Radice
FROM Ric

Una volta ottenuta la query ricorsiva, si mostrano i risultati tramite un SELECT.

WITH Ric (ID, Elemento, ID_Parent, Percorso, Radice)
AS (
    SELECT ID, Elemento, ID_Parent,
    cast('/Terra' as varchar(max)) as Percorso,
    cast('Terra' as varchar(max)) as Radice
    FROM Pianeta
    WHERE ID_Parent IS NULL
    UNION ALL
    SELECT p.ID, p.Elemento, p.ID_Parent,
    Ric.Percorso + '/' + p.Elemento as Percorso, Ric.Radice
    FROM @p AS p INNER JOIN Ric on p.ID_Parent = Ric.ID
)
SELECT ID, Elemento, Percorso, Radice
FROM Ric