Re: Ltree - how to sort nodes on parent node

On 19 Apr 2010, at 20:26, cojack wrote:

>> Alban Hertroys wrote:
>>
>> It would help if you'd show us what result you expect from ordering the
>> above.
>>
>> Most people would order this by path I think. However that doesn't match
>> your sort column and I can't think of any method that would give results
>> in such an arbitrary order as you seem to be specifying - unless you set
>> it by hand like you do.

> Yes, you have right, for example I create new idea of stored data in table:
>
> here is a paste: http://pastebin.com/4pX5cM7j -- never expired link
>
> As you can see, I have noodes with numeric type, those nodes present a sort
> position by self. And If I type ORDER BY path; I will have data like I want
> to have: http://pastebin.com/R4z01LC5 -- never expired link
>
> Again, you can see now grouped data in his nodes, this is the outputed data
> I wanted. If you know better way to make this WITHOUT recursive queries,
> give me a hint.


Aha, looks like you want to sort each tree level by some user-specified order.

You should realise that ltree was contributed before Postgres supported (recursive) CTE's. If you're using ltree in
combinationwith recursive CTE's you're doing twice the work that you need to do - ltree was created as a means to make
recursivequeries possible in the first place. 

I think you have basically two ways to go about this:

1). The way you're doing this in your new examples should work, although I'd probably make the ordering numbers part of
thecategory names and split those off when I read them. For example: 
         27 | 1|Top
         28 | 1|Top.1|Science
         29 | 1|Top.2|Hobby
         30 | 1|Top.3|Colors
         31 | 1|Top.1|Science.1|Physics
         32 | 1|Top.1|Science.2|Chemistry
         33 | 1|Top.1|Science.3|Biology
         34 | 1|Top.1|Science.4|History
         35 | 1|Top.2|Hobby.1|Fishing
         36 | 1|Top.2|Hobby.2|Football
         37 | 1|Top.3|Colors.1|Black
         38 | 1|Top.3|Colors.2|Red
         39 | 1|Top.3|Colors.3|Blue
         40 | 1|Top.1|Science.5|Archeology
         41 | 1|Top.2|Hobby.3|Swimming
         42 | 1|Top.3|Colors.4|Gray
         43 | 1|Top.3|Colors.5|Purple
         44 | 1|Top.3|Colors.6|Brown
         45 | 1|Top.2|Hobby.4|Climbing

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;

I haven't actually used recursive CTE's before so there may be some errors in the above, but you get the general idea.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bcd773910411833268189!