Re: Ltree - how to sort nodes on parent node

On 20 Apr 2010, at 11:59, cojack wrote:

>> 1). The way you're doing this in your new examples should work, although
>> I'd probably make the ordering numbers part of the category 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

> My and your first example doesn't work fine at all, why? Becouse when we add
> more thank 10 sub nodes in some node, the 10 node will not be after 9, but

That's just a matter of reserving enough padding for the numbers to fit. It does mean you bake in an upper limit to the
numberof items people can sort, but there is a practical limit your users are very unlikely to ever pass. I think
anythingpast 4 digits is unlikely to happen. It's not a very clean solution, but it certainly does work. 

> after 1 before 2, and this is not good idea to set sort in path. I think the
> best idea for this will be create other column, with also ltree data type
> and stored inside a sort/ordering data. Like:
>
> 1
> 1.1
> 1.1.1
> 1.1.2
> 1.1.3
>
> And while selected it from table, just cast it to int. I'll check this and
> his performance after I return from work.

This has the same problem as the previous one, 10 will end up between 1 and 2. It is cleaner than combining both into
onetree though, so with sufficient padding it should work. 

> I am not interested about recursive queries, i think this kill ltree idea.


And IMHO it should. ltree is from a time when we didn't have any other means to describe data organised as a tree in
Postgres.Navigating a tree is inherently recursive, so recursion is most likely the proper way to go about it. 

A solution omitting recursion (like ltree) can be faster, but you will run into limitations like the one you're
currentlystruggling with. 

A solution with recursive queries will probably be more flexible and allows for referential integrity without having to
writeyour own triggers and stuff - for example, what happens if you decide that Archeology isn't a Science but a
Colour?What makes sure it's child-nodes get moved into Colors as well? 

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,4bcdf97810413554942613!