Re: Sorting with materialized paths

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