Search Postgresql Archives

Re: Ltree - how to sort nodes on parent node

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux