Re: Ltree - how to sort nodes on parent node

On 20 Apr 2010, at 18:05, Harald Fuchs wrote:

> 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

May be, but then you're just re-inventing ltree again. I'm pretty sure this must be possible without adding convoluted things like casting sort orders to text (which can for example cause issues like '10' ending up between '1' and '2').

Since this is 8.4 anyway (CTE's after all), can't the sorting be done using a windowing function or something? We have recursion now, there's got to be a proper solution, I just can't get my mind around it right now.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4bcdf5a610412270627163!