On 08/07/2014 01:22 PM, Gregory Taylor wrote: > I got this recommendation from someone else, and think that it's > probably the way to go. I've been playing with it unsuccessfully so far, > though. Most certainly because I've got something weirded up. Here's > what I have: > > > WITH RECURSIVE cte ( > id, discussion_id, body, num_votes, > class_section_id, modified_time, > author_id, reply_parent_id, > path, votes_path, depth > ) AS ( > SELECT discussion_response.id <http://discussion_response.id>, > discussion_response.discussion_id, > discussion_response.body, discussion_response.num_votes, > discussion_response.last_edited_time, > discussion_response.class_section_id, > discussion_response.author_id, > discussion_response.reply_parent_id, > array[id] AS path, > array[num_votes, id] AS votes_path, > 1 AS depth > FROM discussion_response > WHERE reply_parent_id IS NULL AND discussion_id=2763 > > UNION ALL > > SELECT discussion_response.id <http://discussion_response.id>, > discussion_response.discussion_id, > discussion_response.body, discussion_response.num_votes, > discussion_response.last_edited_time, > discussion_response.class_section_id, > discussion_response.author_id, > discussion_response.reply_parent_id, > cte.path || discussion_response.id > <http://discussion_response.id>, > cte.votes_path || discussion_response.num_votes || > discussion_response.id <http://discussion_response.id>, > cte.depth + 1 AS depth > FROM discussion_response > JOIN cte ON discussion_response.reply_parent_id = cte.id > <http://cte.id> > WHERE discussion_response.discussion_id=2763 > ) > SELECT * FROM cte ORDER BY votes_path DESC, path DESC LIMIT 50 OFFSET 0; > > The problem with this is that non-root level (depth > 1) rows end up at > the top because of the ordering by votes_path. For example: > > id=292839, num_votes=0, reply_parent_id=211957, > votes_path={2,211957,0,292839}, path={211957,292839}, depth=2 > id=211957, num_votes=2, reply_parent_id=NULL, votes_path={2,211957}, > path={211957}, depth=1 > > I understand why it is ordered this way, it's just not what I was hoping > for. Ideally this ends up like this: > > id=211957, num_votes=2, reply_parent_id=NULL, votes_path={2,211957}, > path={211957}, depth=1 > id=292839, num_votes=0, reply_parent_id=211957, > votes_path={2,211957,0,292839}, path={211957,292839}, depth=2 > > Sorting by path causes the correct "tree" structure to be returned and > in the right order, but obviously it's not > sorted at all by votes. 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; -- Vik -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
