Temat: Postgres - struktura drzewiasta i problem z WITH RECURSIVE

Jest tak struktura i dane tak wstawione.

CREATE TABLE taxonomy

    (

    id integer NOT NULL CONSTRAINT taxPK PRIMARY KEY,

    value VARCHAR(255),

    parent_id integer

    );

ALTER TABLE

    taxonomy

ADD CONSTRAINT

    taxFK

FOREIGN KEY

    (parent_id)

REFERENCES

    taxonomy(id);



INSERT INTO taxonomy VALUES (0,'zero',NULL),(1,'one',0),(2,'two',0),(3,'three',0),(4,'four',1),(5,'five',2),(6,'six',2);

Zapytanie ponizsze nie dziala prawidlowo

WITH RECURSIVE cte AS (

   SELECT id, value,parent_id, 1 AS level

   FROM   taxonomy

   WHERE  id = 0



   UNION  ALL

   SELECT t.id, t.value,t.parent_id, c.level + 1

   FROM   taxonomy t

   JOIN   cte      c ON t.parent_id = c.id

   )

SELECT value

FROM   cte

ORDER  BY level;

Zapytanie zwraca rekordy w takiej kolejnosci w jakiej zostaly wstawione do tabeli. Jesli sie je wstawi w kolejnosci takiej jak wynika ze struktury drzewa to zapytanie zadziala, ale jesli wstawi sie je tak jak w przykladzie to nie dziala.
Zwracany wynik:
zero
one
two
three
four
five
six

Pozadany wynik:
zero
one
four
two
five
six
three

Czy ktos wie w czym moze byc problem?

Temat: Postgres - struktura drzewiasta i problem z WITH RECURSIVE

Znalazlem rozwiazanie, chodzi o slowo-klucz "path", zapytanie powinno wygladac tak

WITH RECURSIVE cte AS (

   SELECT id, value,parent_id, 1 AS level,array[id] AS path

   FROM   taxonomy

   WHERE  id = 0

   UNION  ALL

   SELECT t.id, t.value,t.parent_id, c.level + 1, (c.path || t.id)

   FROM   taxonomy t

   JOIN   cte      c ON t.parent_id = c.id

   )

SELECT value

FROM   cte

ORDER  BY path;

Poprzednie zapytanie dziala jednak dobrze ale nie takiego efektu sie po nim spodziewalem ;) Zwraca rekordy w takiej kolejnosci jaka wynika z level po ktorym jest sortowanie - jest to tzw. Breadth-first search. W tym przypadku odpowiada to kolejnosci wstawienia rekordow do tabeli. Jesli chce sie miec sortowanie w postaci zagniezdzenia (depth-first search) to trzeba zrobic sciezke i po niej sortowac.Ten post został edytowany przez Autora dnia 10.11.16 o godzinie 10:21