Retrieve number of replies to a comment

Question

I have the following PostgreSQL table to store comments on different posts:

Comment table:

commentID | parentID | postID | content | author | created

I want now to retrieve with a single Query, given a postID, all comments to the post AND the number of replies each comment has. A comment is a reply if the parentID is not null and equals then the commentID of its parent.

I tried something like the following where I join the table on its self and look for the matches of parentID = commentID but I could not get it working and would appreciate some help :)

SELECT comments.commentID as commentID, comments.parentID as parentID, comments.postID as postID,
comments.content as content, comments.author as author, comments.created as created,
COALESCE(c1.numReplies, 0) as numReplies
FROM comments c0
LEFT JOIN (
    SELECT parentID, COUNT(*) FILTER (WHERE postID = :givenPostID) as numReplies as numReplies
    FROM comments
) c1 on c0.commentID = c1.parentID
WHERE c0.postID = :givenPostID;

Answer 1

This looks like a good spot for a subquery, or a lateral join:

select c.*, c1.no_replies
from comments c
cross join lateral (
    select count(*) no_replies
    from comments c1
    where c1.parentid = c.commentid
) c1 
where c.postid = :givenpostid

Side note - the query you wanted to write probably is:

SELECT c0.*, COALESCE(c1.numReplies, 0) as numReplies
FROM comments c0
LEFT JOIN (
    SELECT parentID, COUNT(*) as numReplies
    FROM comments
    GROUP BY parentID
) c1 on c0.commentID = c1.parentID
WHERE c0.postID = :givenPostID

The subquery is slightly different: first, it needs a GROUP BY clause just to be valid SQL; and also, there is no need for a conditional count.