Re: Recursive CTE trees + Sorting by votes

On Thu, Aug 7, 2014 at 8:12 AM, Vik Fearing <vik.fearing@dalibo.com> wrote:
>
> Just export the order from your CTE.
>
> WITH RECURSIVE tree AS (
>     SELECT dr.id,
>            ...,
>            array[dr.id] as path,
>            1 as depth,
>            row_number() over (order by dr.num_votes desc) as sort_order
>     FROM discussion_response AS dr
>     WHERE dr.reply_parent_id IS NULL
>       AND dr.discussion_id = 2763
>
>     UNION ALL
>
>     SELECT dr.id,
>            ...,
>            tree.path || dr.id,
>            tree.depth + 1
>            row_number() over (order by dr.num_votes desc)
>     FROM discussion_response AS dr
>     JOIN tree ON tree.id = dr.reply_parent_id
>     WHERE NOT array[dr.id] <@ tree.path
> )
> SELECT *
> FROM tree
> ORDER BY depth, sort_order
> LIMIT 50;
>

It looks like this clobbers the hierarchy by sorting by depth first. I'm
trying to preserve said hierarchy so I can paginate using OFFSET/LIMIT
easily. I'm not sure what I'm shooting for is even possible, though.

-- 
Greg Taylor
http://gc-taylor.com