查询得到最有反应的帖子及其反应

Question

我有一张名为posts的桌子：

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

但看来我在这件事上无路可走。我用它作为子查询来得到最大的喜欢、心和厌恶，但这似乎是错误的，因为它们似乎都有相同的数字。

更新

“多亏了先生，”马丁说：

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

Answer 1

编辑了下面的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

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

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