评论和回复与Postgres

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

Comments and replies with Postgres

问题

1

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

  1. SELECT
  2.   p.postid,
  3.   p.body AS post_body,
  4.   c.commentid,
  5.   c.body AS comment_body,
  6.   r.replyid,
  7.   r.body AS reply_body
  8. FROM posts AS p
  9. LEFT JOIN (
  10.   SELECT
  11.     commentid,
  12.     postid,
  13.     body,
  14.     created_at
  15.   FROM comments
  16.   WHERE postid = 1
  17.   ORDER BY created_at
  18.   LIMIT 2
  19. ) AS c ON p.postid = c.postid
  20. LEFT JOIN (
  21.   SELECT
  22.     replyid,
  23.     commentid,
  24.     body,
  25.     created_at
  26.   FROM replies
  27.   ORDER BY created_at
  28.   LIMIT 2
  29. ) AS r ON c.commentid = r.commentid
  30. WHERE p.postid = 1;

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

2

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

  1. SELECT
  2.   p.postid,
  3.   p.body AS post_body,
  4.   c.commentid,
  5.   c.body AS comment_body,
  6.   (
  7.     SELECT COUNT(*)
  8.     FROM comments
  9.     WHERE postid = p.postid
  10.   ) AS comments_aggregate
  11. FROM posts AS p
  12. LEFT JOIN (
  13.   SELECT
  14.     commentid,
  15.     postid,
  16.     body,
  17.     created_at
  18.   FROM comments
  19.   WHERE postid = 1
  20.   ORDER BY created_at
  21.   LIMIT 2
  22. ) AS c ON p.postid = c.postid
  23. 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,

  1. const POST =
  2.     fetch(
  3.          select * from posts
  4.          where postid = 1
  5.     )
  1. const COMMENTS =
  2.     fetch(
  3.          select * from comments
  4.          where postid = ${POST.postid}
  5.          order by created_at
  6.          limit 2
  7.     )
  1. const REPLIES =
  2.     COMMENTS.map((COMMENT) => {
  3.         fetch(
  4.              select * from replies
  5.              where commentid = ${COMMENT.commentid}
  6.              order by created_at
  7.              limit 2
  8.         )
  9.     })

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

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

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

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

答案1

得分: 1

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

  1. SELECT json_build_object(
  2.     'postid', p.postid,
  3.     'body', p.body,
  4.     'comments', json_agg(
  5.         json_build_object(
  6.             'commentid', c.commentid,
  7.             'body', c.body,
  8.             'replies', replies,
  9.             'reply_aggregate', replycount
  10.         )
  11.     ),
  12.     'comments_aggregate', CommentCount
  13. )
  14. FROM posts p
  15. LEFT OUTER JOIN (
  16.     SELECT postid, commentid, body, created_at, CommentCount
  17.     FROM (
  18.         SELECT *,
  19.         /* Count the total number of comments per post */
  20.         COUNT(*) OVER (PARTITION BY postid) AS CommentCount,
  21.         /* Number the comments of a post to later select the first 2 */
  22.         ROW_NUMBER() OVER (PARTITION BY postid ORDER BY created_at) as CommentNumber
  23.         FROM Comments
  24.     ) FirstTwoComments
  25.     WHERE CommentNumber &lt;= 2
  26. ) c ON p.postid = c.postid
  27. LEFT OUTER JOIN (
  28.     SELECT commentid,
  29.         ReplyCount,
  30.         json_agg(
  31.             json_build_object(
  32.                 'replyid', replyid,
  33.                 'body', body
  34.             )
  35.         ) AS replies
  36.     FROM (
  37.         SELECT *,
  38.         /* Count the total number of replies per comment */
  39.         COUNT(*) OVER (PARTITION BY commentid) AS ReplyCount,
  40.         /* Number the replies of a comment to later select the first 2 */
  41.         ROW_NUMBER() OVER (PARTITION BY commentid ORDER BY created_at) as CommentNumber
  42.         FROM Replies
  43.     ) FirstTwoReplies
  44.     WHERE CommentNumber &lt;= 2
  45.     GROUP BY commentid, ReplyCount
  46. ) r ON c.commentid = r.commentid
  47. WHERE p.postid = 1
  48. 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.

  1. SELECT json_build_object(
  2. 	     &#39;postid&#39;, p.postid,
  3. 	     &#39;body&#39;, p.body,
  4. 	     &#39;comments&#39;, json_agg(
  5.            json_build_object(
  6.              &#39;commentid&#39;, c.commentid,
  7.              &#39;body&#39;, c.body,
  8.              &#39;replies&#39;, replies,
  9. 			 &#39;reply_aggregate&#39;, replycount
  10.            )
  11.          ),
  12. 		&#39;comments_aggregate&#39;, CommentCount
  13. 	   )
  14. FROM posts p
  15. LEFT OUTER JOIN (
  16. 	SELECT postid, commentid, body, created_at, CommentCount
  17. 	FROM (
  18. 		SELECT *,
  19. 		/* Count the total number of comments per post */
  20. 		       COUNT(*) OVER (PARTITION BY postid) AS CommentCount,
  21. 		/* Number the comments of a post to later select the first 2 */
  22. 		       ROW_NUMBER() OVER (PARTITION BY postid ORDER BY created_at) as CommentNumber
  23. 		FROM Comments
  24. 	) FirstTwoComments
  25. 	WHERE CommentNumber &lt;= 2
  26. ) c ON p.postid = c.postid
  27. LEFT OUTER JOIN (
  28. 	SELECT commentid,
  29. 	       ReplyCount,
  30. 		   json_agg(
  31.              json_build_object(
  32.                &#39;replyid&#39;, replyid,
  33.                &#39;body&#39;, body
  34.              )
  35.            ) AS replies
  36. 	FROM (
  37. 		SELECT *,
  38. 		/* Count the total number of replies per comment */
  39.                COUNT(*) OVER (PARTITION BY commentid) AS ReplyCount,
  40. 		/* Number the replies of a comment to later select the first 2 */
  41.                ROW_NUMBER() OVER (PARTITION BY commentid ORDER BY created_at) as CommentNumber
  42. 		FROM Replies
  43. 	) FirstTwoReplies
  44. 	WHERE CommentNumber &lt;= 2
  45. 	GROUP BY commentid, ReplyCount
  46. ) r ON c.commentid = r.commentid
  47. WHERE p.postid = 1
  48. GROUP BY p.postid, p.body, CommentCount

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

发表评论

匿名网友

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

确定