I have sql query to find all replies from orig comment:
WITH RECURSIVE children AS ( SELECT blog_comments_comment.*, 0 AS relative_depth FROM blog_comments_comment WHERE id in %s UNION ALL SELECT blog_comments_comment.*, children.relative_depth + 1 FROM blog_comments_comment,children WHERE blog_comments_comment.reply_to_id = children.id ) SELECT id,post_id, name,body, reply_to_id, relative_depth FROM children ORDER BY children;
How can I sort it to have it like this?
-main comment ---reply to main comment ------reply to reply ----------etc ---2nd reply to main comment ------reply to 2nd reply ----------etc
Advertisement
Answer
Maintain the path and sort:
WITH RECURSIVE children AS ( SELECT bcc.*, 0 AS relative_depth, array[bcc.id] as path FROM blog_comments_comment bcc WHERE id in %s UNION ALL SELECT bcc.*, c.relative_depth + 1, path || c.id FROM children c JOIN blog_comments_comment bcc ON bcc.reply_to_id = bcc.id ) SELECT id,post_id, name,body, reply_to_id, relative_depth FROM children ORDER BY path;
Here is a db<>fiddle.