Re: Ltree - how to sort nodes on parent node

In article <59670B22-30CB-4E6E-83C8-C1D1036C9B2A(at)solfertje(dot)student(dot)utwente(dot)nl>,
Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> writes:

> 2). Drop the ltree column and go with a truly recursive approach, something like this:

> CREATE TABLE node (
> category text NOT NULL PRIMARY KEY,
> sort_order int NOT NULL,
> parent text REFERENCES tree (category)
> ON UPDATE CASCADE
> ON DELETE CASCADE
> );

> WITH RECURSIVE tree AS (
> SELECT *
> FROM node
> WHERE parent IS NULL

> UNION ALL

> SELECT node.*
> FROM tree, node
> WHERE node.parent = tree.category
> ORDER BY sort_order
> )
> SELECT * FROM tree;

Here's a working version:

WITH RECURSIVE tree (path, category, sort_order, parent) AS (
SELECT category, category, sort_order::text, parent
FROM node
WHERE parent IS NULL
UNION ALL
SELECT t.path || '.' || n.category,
n.category,
t.sort_order || '.' || n.sort_order,
n.parent
FROM tree t
JOIN node n ON n.parent = t.category
)
SELECT path
FROM tree
ORDER BY sort_order