Google+ Followers

zaterdag 29 december 2012

Recursie met CTE's


Het ophalen van een pad in een simpele pappa/kindje hierarchie vereist recursie en recursie vereist veel queries, tenminste als je database zelf geen recursie ondersteunt.

PostgreSQL ondersteunt recursie.

Dit maakt het mogelijk om de paden op te halen via een simpel CTE statement:

DROP TABLE IF EXISTS treedemo;
CREATE TABLE treedemo (nodeid integer, parentnodeid integer, title varchar(25));
INSERT INTO treedemo VALUES
(1,null,'root'), (2,1,'hardware'),(3,2,'intel'),(4,1,'software'),(5,4,'linux'),(6,4,'windows');


WITH RECURSIVE tree AS
(
SELECT treedemo.nodeid, treedemo.parentnodeid, treedemo.title, CAST(treedemo.title AS TEXT) AS path FROM treedemo WHERE nodeid=1
UNION ALL
SELECT treechildren.nodeid, treechildren.parentnodeid, treechildren.title, tree.path || '/' || treechildren.title AS path
FROM treedemo AS treechildren
INNER JOIN tree ON treechildren.parentnodeid = tree.nodeid
)
SELECT * FROM tree;

Ok, simpel als je weet hoe CTE's werken. Alles tussen de halen achter AS is de CTE en daarin gebeurt de magie.

Die magie bestaat in een SELECT die het root-record ophaalt, het record waar het pad begint. In dit geval de root-node.
Aal die query wordt een SELECT statement gejoined die record ophaalt op basis van een JOIN met de CTE zelf. Dat is dus de recursie, de JOIN pakt alle records waarvan het parentid gelijk is aan het nodeid wat eerder in de CTE is geselecteerd.

Gevolg, een net setje records met een slash-separated string met de titles van de nodes. Ditzelfde geintje kun je doen voor de ID's als je wilt.

zondag 23 december 2012

Tipje, packt-korting

Op de valreep nog een tipje, packt-publishing, uitgever van diverse goede naslagwerken over o.a. PostgreSQL, houdt tot begin januari een kortingsactie waardoor eBooks weg mogen voor slechts  $5 per stuk.

Kun je je eReader mooi nog even volgooie voor een derde van de prijs.

http://www.packtpub.com/