Postgresql - Reformat Query return order

Question

I have a Recursive Query that returns a non linear tree, but it returns all of the rootnodes first. (where parent_id is null)

How would I order the rows in which my query gets one rootnode, gets the entire tree, followed by getting the second rootnode and then getting that entire tree...so on etc.

With RECURSIVE cte AS
(
   (
    SELECT * 
    FROM comments 
    WHERE thread_id = 1
      AND parent_id is NULL 
    ORDER BY upvoted DESC 
    FETCH FIRST 10 ROW ONLY
   )
   UNION
   SELECT t.*
   From comments t
     JOIN cte rt ON rt.comment_id = t.parent_id
 )
  SELECT cte.comment_id, cte.date_posted, cte.posted_by, cte.posted_by_user, cte.thread_id,
            cte.parent_id, cte.comments, cte.post_title, cte.posted_to_group, cte.upvoted, cte.downvoted,
            cte.depth, cv.user_id, cv.vote, cv.thread_id
     from cte LEFT JOIN
          commentsvoted cv
          ON cte.comment_id = cv.id
          AND cv.user_id = '82411580-6355-490e-be79- e7db9f561f66'

If I add:

ORDER BY coalesce(parent_id, comment_id), comment_id

to my last select, it returns what I have described except it ignores:

ORDER BY upvoted DESC

Gordon Linoff · Accepted Answer · 2019-06-20 14:58:50Z

1

You can use window functions in the order by:

ORDER BY MAX(upvoted) OVER (PARTITION BY coalesce(parent_id, comment_id)) DESC, 
         coalesce(parent_id, comment_id),   -- keep this in the event of ties,
         (case when parent_id is null then 1 else 0 end),
         comment_id

edited Jun 20, 2019 at 14:58

answered Jun 20, 2019 at 14:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WHOATEMYNOODLES Over a year ago

This causes the parent_id's to get out of order

Gordon Linoff Over a year ago

@davids. . . . I think your original code had the same problem, but you may not have seen it for your particular result set. I added an additional condition which should put the parents first.

WHOATEMYNOODLES Over a year ago

Thanks for the help, but I just can't seem to figure out what's wrong. I think I'm going to filter out the rows in JSON instead

Collectives™ on Stack Overflow

Postgresql - Reformat Query return order

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related