Ovid wrote on 09.05.2010 15:33:
My apologies. This isn't PG-specific, but since this is running on
PostgreSQL 8.4, maybe there are specific features which might help.
I have a tree structure in a table and it uses materialized paths to
allow me to find children quickly. However, I also need to sort the
results depth-first, as one would expect with threaded forum
replies.
id | parent_id | matpath | created
----+-----------+---------+----------------------------
2 | 1 | 1 | 2010-05-08 15:18:37.987544
3 | 1 | 1 | 2010-05-08 17:38:14.125377
4 | 1 | 1 | 2010-05-08 17:38:57.26743
5 | 1 | 1 | 2010-05-08 17:43:28.211708
7 | 1 | 1 | 2010-05-08 18:18:11.849735
6 | 2 | 1.2 | 2010-05-08 17:50:43.288759
9 | 5 | 1.5 | 2010-05-09 14:02:43.818646
8 | 6 | 1.2.6 | 2010-05-09 14:01:17.632695
So the final results should actually be sorted like this:
id | parent_id | matpath | created
----+-----------+---------+----------------------------
2 | 1 | 1 | 2010-05-08 15:18:37.987544
6 | 2 | 1.2 | 2010-05-08 17:50:43.288759
8 | 6 | 1.2.6 | 2010-05-09 14:01:17.632695
3 | 1 | 1 | 2010-05-08 17:38:14.125377
4 | 1 | 1 | 2010-05-08 17:38:57.26743
5 | 1 | 1 | 2010-05-08 17:43:28.211708
9 | 5 | 1.5 | 2010-05-09 14:02:43.818646
7 | 1 | 1 | 2010-05-08 18:18:11.849735
Try this:
with recursive thread_display (id, parent_id, matpath, created, sort_key)
as
(
select id, parent_id, matpath, created, array[id] as sort_key
from threads
where id = 1
union all
select c.id, c.parent_id, c.matpath, c.created, p.sort_key||array[c.id]
from threads c
join thread_display p on c.parent_id = p.id
)
select id, parent_id, matpath, created
from thread_display
order by sort_key;
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
