Re: LTREE extension and "order by"

Hi.

You should convert path to integer[].

2011/7/20, Ivan Polak <ivan(dot)polak(at)f4s(dot)sk>:
> Hi,
>
> in postgreSQL (with LTREE extension) database I have the following
> table "comments":
>
> id BIGINT /* id */
> article_id BIGINT /*article-id */
> parent_id BIGINT
> comment TEXT
> path LTREE
> level INTEGER /* level */
>
> with the following rows:
>
> id article_id comment parent_id path level
> 1 1 aaaa 1 1
> 2 1 bbbb 1 1.2 2
> 3 1 cccc 2 1.2.3 3
> 4 1 dddd 2 1.4 2
> 5 1 eeee 4 1.4.5 3
> 6 1 ffff 6 1
> 7 1 gggg 6 6.7 2
> 8 1 hhhh 6 6.8 2
> 9 1 iiii 9 1
> 10 1 jjjj 10 1
> 11 1 kkkk 5 1.4.5.11 4
>
> and I need to select complete tree (with correct order of comments).
>
> SELECT * from comments where article_id = 2 order by <???>
>
> when I used:
>
> SELECT * from comments where article_id = 2 order by path
>
> the result is:
>
> id comment path
> 1 aaaa 1
> 2 bbbb 1.2
> 3 cccc 1.2.3
> 4 dddd 1.4
> 5 eeee 1.4.5
> 11 kkkk 1.4.5.11
> 10 jjjj 10
> 6 ffff 6
> 7 gggg 6.7
> 8 hhhh 6.8
> 9 iiii 9
>
> BUT, it is wrong, because comment with id = 10 is after comment with id=11
>
> (i know, this is correct, because ordering by column PATH [as TEXT],
> and 10 is 'after' 1.4.5.11)
>
> , but I need :
>
> id comment path
> 1 aaaa 1
> 2 bbbb 1.2
> 3 cccc 1.2.3
> 4 dddd 1.4
> 5 eeee 1.4.5
> 11 kkkk 1.4.5.11
> 6 ffff 6
> 7 gggg 6.7
> 8 hhhh 6.8
> 9 iiii 9
> 10 jjjj 10
>
> thanks
>
> Ivan
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--
------------
pasman