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
