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
