Comments and replies with Postgres

I have the SQL database tables posts, comments, and replies

posts

comments

replies

I want to do 2 things

1

We are GIVEN the postid. For instance, postid=1.

In a single call to the database, I want to query the database to GET:

1. the post body
2. the first 2 comments on a post, sorted by created_at
3. for each of those comments, get the first 2 replies, sorted by created_at

Querying POST, COMMENTS, and REPLIES from the database using SQL would look something like,

const POST =
    fetch(
         select * from posts
         where postid = 1
    )

const COMMENTS =
    fetch(
         select * from comments
         where postid = ${POST.postid}
         order by created_at
         limit 2
    )

const REPLIES =
    COMMENTS.map((COMMENT) => {
        fetch(
             select * from replies
             where commentid = ${COMMENT.commentid}
             order by created_at
             limit 2
        )
    })

How do I write these queries as a SINGLE SQL call to the database?

The returned data should be in a nested form. Something like

const POST = {
    postid: 1
    body: "...",
    comments: [{commentid: 1, body: "..."}, ...]
}

But if you have a different form that is easier, I'm open.

2

In the call above, how do I include the comments and replies aggregates?

For instance, the returned data should look like

const POST = {
    // same as before
    postid: 1
    body: "...",
    comments: [{commentid: 1, body: "..."}, ...]
    // aggregate for comments
    comments_aggregate: 2
}

Your schema is incorrect. You cannot reference column commentid if it is not unique. Assuming you properly make the postid, commentid, replyid the respective PRIMARY KEY of their tables, this method should provide the information you are after.<br/>
Few comments inline.

SELECT json_build_object(
	     'postid', p.postid,
	     'body', p.body,
	     'comments', json_agg(
           json_build_object(
             'commentid', c.commentid,
             'body', c.body,
             'replies', replies,
			 'reply_aggregate', replycount
           )
         ),
		'comments_aggregate', CommentCount
	   )
FROM posts p
LEFT OUTER JOIN (
	SELECT postid, commentid, body, created_at, CommentCount
	FROM (
		SELECT *,
		/* Count the total number of comments per post */
		       COUNT(*) OVER (PARTITION BY postid) AS CommentCount,
		/* Number the comments of a post to later select the first 2 */
		       ROW_NUMBER() OVER (PARTITION BY postid ORDER BY created_at) as CommentNumber
		FROM Comments
	) FirstTwoComments
	WHERE CommentNumber <= 2
) c ON p.postid = c.postid
LEFT OUTER JOIN (
	SELECT commentid,
	       ReplyCount,
		   json_agg(
             json_build_object(
               'replyid', replyid,
               'body', body
             )
           ) AS replies
	FROM (
		SELECT *,
		/* Count the total number of replies per comment */
               COUNT(*) OVER (PARTITION BY commentid) AS ReplyCount,
		/* Number the replies of a comment to later select the first 2 */
               ROW_NUMBER() OVER (PARTITION BY commentid ORDER BY created_at) as CommentNumber
		FROM Replies
	) FirstTwoReplies
	WHERE CommentNumber <= 2
	GROUP BY commentid, ReplyCount
) r ON c.commentid = r.commentid
WHERE p.postid = 1
GROUP BY p.postid, p.body, CommentCount