mysql - 查询 react 计数

Question

我有 table :

用户

user_id
name

帖子

post_id
user_id
post
image_path

react

reaction_id
post_id
reaction
user_id

Reactions have values of {1: 'like', 2: 'dislike'}

现在我想获取每个 react 中具有相应计数的所有帖子。

所以最终结果是这样的:

{
post_id: 1,
post: "Hello World",
image_path: "public/upload_123.png",
likes: 23,
dislikes: 18,
my_reaction: 1
}

Answer 1

您可以通过使用 LEFT 连接(以防万一帖子没有反应)连接表，然后使用条件聚合来完成此操作:

select p.post_id, p.post, p.image_path,
  sum(r.reaction = 1) likes,
  sum(r.reaction = 2) dislikes
from posts p left join reactions r
on r.post_id = p.post_id
group by p.post_id, p.post, p.image_path