LTREE extension and "order by"
От | Ivan Polak |
---|---|
Тема | LTREE extension and "order by" |
Дата | |
Msg-id | CAN=kwkvn62omh-hQ4brmynuUMXmAH3NVx3SV-aLjJqJHa2TdSA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: LTREE extension and "order by"
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: