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