CODEWITHSUNDEEP
sqlpostgresql
Milez Noles
Milez Noles

Reputation: 33

how to sort recursive query in PostgreSQL

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

Upvotes: 2

Views: 209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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.

Upvotes: 2

Related Questions