Rekursiv hämtning av information med kravuppfyllnad

Nedan följer en frågeställning som är baserat på ett faktiskt problem jag stötte på häromveckan.

I princip så går problemet ut på att jag vill rekursera genom ett träd i SQL, och ifall vissa kriterier uppfylls så vill jag fortsätta rekursera, och under vissa omständigheter vill jag inte det.

Trädet

Trädstrukturen är mycket simpel i vårat exempel. Varje nod har ett ID och en förälder.

CREATE TABLE node (
  id int,
  parent_id int
 );

I exemplet så ser strukturen ut som följer:

- 1
  |- 11
  |  |- 111
  |  |- 112
  |  |- 113
  |
  |- 12
     |- 121
     |- 122
     |- 123

nod ‘121’ har här föräldern ‘12’, som i sin tur har föräldern ‘1’.

För att rekursera uppåt genom trädet kan vi t.ex. använda följande query (i Postgres), där XXXX är id't på den nod vi vill börja ifrån.

WITH RECURSIVE ancestors
	AS  (SELECT t1.id, t1.parent_id
            FROM node t1
            WHERE t1.id = XXXX
		UNION ALL
		SELECT
            t2.id, t2.parent_id
            FROM ancestors n
            INNER JOIN node t2 ON t2.id = n.parent_id
	)    
SELECT ancestors.id
FROM ancestors

Attribut

Varje nod kan ha ett eller flera attribut kopplade till sig. Attributen har ett namn och ett värde.

Vissa av attributen är markerade som inheritable, vilket betyder att de kan ärvas av noder som ligger nedanför noden i strukturen. Attribut ärvs endast ifall noden själv inte definierar ett värde för det attributet.

CREATE TABLE attributes (
   node_id int,
   name varchar(25),
   val varchar(25),
   inherit bool
 );

För att hämta både nodstrukturen och attribut kan t.ex. nedanstående query användas. Den kommer att hämta samtliga attribut på den nedersta noden, sedan gå upp till föräldernoden och hämta samtliga attribut därifrån, etc.

WITH RECURSIVE ancestors
	AS  (SELECT t1.id, t1.parent_id
		FROM node t1
	 	WHERE t1.id = XXXX
		UNION ALL
		SELECT
		t2.id, t2.parent_id
		FROM ancestors n
		INNER JOIN node t2 ON t2.id = n.parent_id
	)    
SELECT ancestors.id,
	attributes.name,
	attributes.val,
    attributes.inherit
FROM ancestors
LEFT JOIN attributes ON attributes.node_id = ancestors.id;

Givet trädstrukturen ovanför, och följande attribut:

|nod | attribut | värde          | ärvs  |
|----|----------|----------------|-------|
|1   | titel    | bastitel       | true  |
|1   | other    | somevalue      | true  |
|12  | titel    | undertitel-12  | true  |
|121 | titel    | undertitel-121 | false |

så vill vi ha ett resultat som endast innehåller de värden som antingen sitter direkt på noden vi är intresserade av, eller som ärvts från någon nod ovanför.

För nod 121 borde värdet för titel alltså vara undertitel-121, medans för nod 111 blir titel bastitel. För båda noderna borde värdet för other vara somevalue (eftersom båda ärver detta attribut från toppnoden).

Utmaningen

Skriv en SQL-sats som endast returnerar de aktuella attributen för en given nod.

Här finns en sqlfiddle med informationen ovan inlagd och klar.