Re: Recursive CTE trees + Sorting by votes - Mailing list pgsql-general

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