On Thu, Aug 7, 2014 at 8:12 AM, Vik Fearing <vik.fearing@xxxxxxxxxx> 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
