Re: Recursive CTE trees + Sorting by votes

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