获取帖子列表的最新 3 条评论

发布于 2024-12-04 05:48:37 字数 567 浏览 3 评论 0原文

目前,我正在运行一个查询来获取某个用户的所有帖子,然后在循环中,我正在查询该特定帖子的最新 3 条评论。效率超级低;我对每个帖子都一遍又一遍地查询。

我想合并我的查询,以便我对所有帖子只查询一次,对这些特定帖子的所有评论只查询一次。目前我有一个逗号分隔的列表,是我为该用户的所有帖子创建的(例如“1,5,18,9”)

posts 表:

  • posts.id
  • posts.userid

comments 表:

  • comments.id
  • comments.relid (这是 postid)
  • comments.userid

查询应该使用我拥有的 $posts_list,这是以逗号分隔的帖子列表。或者对该用户的所有帖子进行子选择,但这似乎效率低下,因为我已经在字符串中包含了帖子列表。

非常感谢您的帮助!

Currently I'm running one query to get all posts for a user, then in the loop for that, I am querying for the latest 3 comments for that particular post. Super inefficient; I'm querying over and over again for every post.

I would like to consolidate my queries so that I query just once for all posts, and just once for all comments for those particular posts. At the moment I have a comma-separated list that I made for all posts for this user (e.g. "1,5,18,9")

posts table:

  • posts.id
  • posts.userid

comments table:

  • comments.id
  • comments.relid (this is the postid)
  • comments.userid

The query should use the $posts_list I have, which is the comma-separated list of posts. Or a subselect for all posts for this user, but that seems inefficient since I already have the post list in a string.

Thanks so much for any help!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

苏别ゝ 2024-12-11 05:48:37

尝试这个查询 -

SELECT p.id, p.userid, c.id, c.userid
  FROM posts p
  JOIN (
    SELECT c1.*, COUNT(*) rank FROM comments c1
    LEFT JOIN comments c2
      ON c2.relid = c1.relid AND c2.id <= c1.id
    GROUP BY c1.relid, c1.id
    ) c
  ON p.id = c.relid
WHERE rank < 4

并添加您需要的条件,即 - WHERE p.userid IN (1,5,18,9)。

Try this query -

SELECT p.id, p.userid, c.id, c.userid
  FROM posts p
  JOIN (
    SELECT c1.*, COUNT(*) rank FROM comments c1
    LEFT JOIN comments c2
      ON c2.relid = c1.relid AND c2.id <= c1.id
    GROUP BY c1.relid, c1.id
    ) c
  ON p.id = c.relid
WHERE rank < 4

And add condition you need, i.e. - WHERE p.userid IN (1,5,18,9).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文