评论和回复与Postgres

huangapple go评论59阅读模式
英文:

Comments and replies with Postgres

问题

1

要在单个数据库调用中检索帖子的正文,前两个按创建时间排序的评论以及每个评论的前两个按创建时间排序的回复,你可以使用以下SQL查询:

SELECT
  p.postid,
  p.body AS post_body,
  c.commentid,
  c.body AS comment_body,
  r.replyid,
  r.body AS reply_body
FROM posts AS p
LEFT JOIN (
  SELECT
    commentid,
    postid,
    body,
    created_at
  FROM comments
  WHERE postid = 1
  ORDER BY created_at
  LIMIT 2
) AS c ON p.postid = c.postid
LEFT JOIN (
  SELECT
    replyid,
    commentid,
    body,
    created_at
  FROM replies
  ORDER BY created_at
  LIMIT 2
) AS r ON c.commentid = r.commentid
WHERE p.postid = 1;

这将返回帖子的正文以及评论和回复的信息,并且可以嵌套为所需的形式。

2

要包括评论和回复的汇总,你可以使用以下SQL查询:

SELECT
  p.postid,
  p.body AS post_body,
  c.commentid,
  c.body AS comment_body,
  (
    SELECT COUNT(*)
    FROM comments
    WHERE postid = p.postid
  ) AS comments_aggregate
FROM posts AS p
LEFT JOIN (
  SELECT
    commentid,
    postid,
    body,
    created_at
  FROM comments
  WHERE postid = 1
  ORDER BY created_at
  LIMIT 2
) AS c ON p.postid = c.postid
WHERE p.postid = 1;

这将返回帖子的正文、评论的信息和评论的数量。评论的数量作为comments_aggregate字段包括在返回的数据中。

英文:

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

posts

postid body created_at
1 The bucks beat the bills 1/16
2 Soccer tricks and tips 1/17

comments

commentid postid(references posts.postid) body created_at
78 1 Yayyy 1/18
79 1 Booo 1/19
79 2 These tips suck 1/20

replies

replyid commentid(references comments.commentid) body created_at
167 79 I agree 1/21
167 78 yayyyy 1/22
168 79 No they dont 1/23

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
}

答案1

得分: 1

你的模式不正确。如果commentid不是唯一的,你就不能引用它。假设你正确地将postidcommentidreplyid分别设置为它们的表的PRIMARY KEY,这个方法应该能提供你所需的信息。<br/>
内联评论如下:

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 &lt;= 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 &lt;= 2
    GROUP BY commentid, ReplyCount
) r ON c.commentid = r.commentid
WHERE p.postid = 1
GROUP BY p.postid, p.body, CommentCount
英文:

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(
	     &#39;postid&#39;, p.postid,
	     &#39;body&#39;, p.body,
	     &#39;comments&#39;, json_agg(
           json_build_object(
             &#39;commentid&#39;, c.commentid,
             &#39;body&#39;, c.body,
             &#39;replies&#39;, replies,
			 &#39;reply_aggregate&#39;, replycount
           )
         ),
		&#39;comments_aggregate&#39;, 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 &lt;= 2
) c ON p.postid = c.postid
LEFT OUTER JOIN (
	SELECT commentid,
	       ReplyCount,
		   json_agg(
             json_build_object(
               &#39;replyid&#39;, replyid,
               &#39;body&#39;, 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 &lt;= 2
	GROUP BY commentid, ReplyCount
) r ON c.commentid = r.commentid
WHERE p.postid = 1
GROUP BY p.postid, p.body, CommentCount

huangapple
  • 本文由 发表于 2023年2月19日 06:35:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/75496802.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定