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 number of items people can sort, but there is a practical limit your users are very unlikely to ever pass. I think anything past 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 one tree 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 currently struggling with.

A solution with recursive queries will probably be more flexible and allows for referential integrity without having to write your 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!