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.