how to sort recursive query in PostgreSQL

Question

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

Gordon Linoff · Accepted Answer · 2021-07-29 12:20:37Z

2

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.

edited Jul 29, 2021 at 12:20

answered Jul 29, 2021 at 11:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

4 Comments

Gordon Linoff Over a year ago

@MilezNoles . . . Can you set up a db<>fiddle? It should be sorting by the path, which should be depth first.

Milez Noles Over a year ago

Thanks for idea, but it still gives not quite right result

Gordon Linoff Over a year ago

@MilezNoles . . . I fixed a typo and added a db<>fiddle.

Milez Noles Over a year ago

"path || bcc.id" this change to your answer solves my problem. Huge thanks to you!

Collectives™ on Stack Overflow

how to sort recursive query in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related