Re: Recursive CTE trees + Sorting by votes

On Thu, Aug 7, 2014 at 8:12 AM, Vik Fearing <vik(dot)fearing(at)dalibo(dot)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