时间戳

首页 > 解决方案 > 查询以获取反应最多的帖子及其反应

mysql - 查询以获取反应最多的帖子及其反应

问题描述

我有一个名为帖子的表：

table.increments(`post_id`).
table.text('post')
table.string('file_path')
table.integer('user_id')

还有一个名为反应的表：

table.increments(`reaction_id`).primary()
table.integer(`post_id`)
table.integer(`user_id`)
table.integer(`reaction`)

我的目标是获得对喜欢、不喜欢和心的反应最多的帖子以及 post_id。

因此，它看起来像：

{
  likes: 300,
  most_liked_post_id: 3,
  most_liked_user_id: 21,
  hearts: 400,
  most_heart_post_id: 4,
  most_heart_user_id: 29,
  dislikes: 300,
  most_disliked_post_id: 3,
  most_disliked_user_id: 21,
}

我试过这个：

SELECT 
  SUM(reaction = 1) AS likes,
  SUM(reaction = 2) AS hearts,
  SUM(reaction = 3) AS dislikes 
FROM
  reactions 
GROUP BY posts_id

但似乎我无处可去。我用它作为子查询来获得最大的喜欢、心和不喜欢，但这似乎是错误的，因为它们似乎都有相同的数字。

更新

感谢@Martin先生，我想到了这个：

SELECT 
  l.posts_id AS like_post,
  l.count AS like_count,
  l.user_id AS like_user_id,
  h.posts_id AS heart_post,
  h.count AS heart_count,
  h.user_id AS heart_user_id,
  d.posts_id AS dislike_post,
  d.count AS dislike_count,
  d.user_id AS dislike_user_id
FROM
  (SELECT 
    posts_id, COUNT(*) AS COUNT, user_id 
  FROM
    reactions 
  WHERE reaction = 1 
  GROUP BY posts_id 
  ORDER BY 2 DESC 
  LIMIT 1) l

  INNER JOIN (SELECT 
    posts_id, COUNT(*) AS COUNT, user_id 
  FROM
    reactions 
  WHERE reaction = 2 
  GROUP BY posts_id 
  ORDER BY 2 DESC 
  LIMIT 1) h

  INNER JOIN (SELECT 
    posts_id, COUNT(*) AS COUNT, user_id 
  FROM
    reactions 
  WHERE reaction = 3 
  GROUP BY posts_id 
  ORDER BY 2 DESC 
  LIMIT 1) d

标签： mysql

解决方案

在 OP 评论之后编辑

我误解了最初的要求，并有一个查询列出了所有帖子的喜欢、心和不喜欢。

现在我更新了查询，只返回最喜欢、最热心和最不喜欢的帖子：

SELECT  a.post_id AS MostLikedPost,
        a.LikeCount,
        b.post_id AS MostHeartedPost,
        b.HeartCount,
        c.post_id AS MostDislikedPost,
        c.DislikeCount
  FROM  (
        SELECT  post_id,
                COUNT(*) AS LikeCount
          FROM  reactions
          WHERE reaction = 1
          GROUP BY post_id
          ORDER BY 2 DESC
          LIMIT 1
        ) a
    LEFT JOIN (
              SELECT  post_id,
                      COUNT(*) AS HeartCount
                FROM  reactions
                WHERE reaction = 2
                GROUP BY post_id
                ORDER BY 2 DESC
                LIMIT 1
              ) b ON 1 = 1
    LEFT JOIN (
              SELECT  post_id,
                      COUNT(*) AS DislikeCount
                FROM  reactions
                WHERE reaction = 3
                GROUP BY post_id
                ORDER BY 2 DESC
                LIMIT 1
              ) c ON 1 = 1

该查询使用三个子查询，每个子查询在每个反应类别中找到最大值。然后将结果合并为单个输出。

注意：代码未经测试，因为没有示例数据或预期输出。

推荐阅读