php - MYSQL查询检查用户是否喜欢

Question

我要求查询语句我有两个表第一个表“帖子”:

post_id     title    Userid
1           test     1

另一个表是“喜欢”

userid  post_id
1       1

我需要单个查询来检查用户是否喜欢这张照片或不批量选择我的解决方案非常糟糕:

SELECT * FROM `table` WHERE x = x LIMIT 100;

并给它foreach并对每一行进行查询:

foreach($results as $v)
{
   $data[1] = $v;
   $data[1]['is_like'] = SELECT COUNT(*) FROM likes WHERE userid = 1;
}

1是已经登录的用户ID

我需要单个查询来返回 post_id,.. 等并像 is_like 一样归档

Answer 1

假设喜欢只能有1行具有相同的(user_id,post_id):

SELECT p.* , 
CASE WHEN 
l.id IS NULL THEN 0
ELSE 1
END as is_liked
FROM posts p
LEFT JOIN likes l ON l.user_id = p.user_id and l.post_id =p.post_id

如果不是(给定 (user_id,post_id) 的 likes 中存在多行):

SELECT p.* , 
CASE WHEN 
l.user_id IS NULL THEN 0
ELSE 1
END as is_liked
FROM posts p
LEFT JOIN 
(
   SELECT DISTINCT user_id,post_id FROM likes 
) l ON l.user_id = p.user_id and l.post_id =p.post_id;

或者

SELECT p.* , 
CASE 
WHEN  EXISTS (SELECT NULL FROM likes l 
  WHERE l.user_id = p.user_id and l.post_id =p.post_id) THEN 1
ELSE 0
END as is_liked
FROM posts p

更新我希望我现在对这个问题有了更好的理解。我的假设:posts.user_id是创建帖子的用户的ID； like 表存储有关谁喜欢该帖子的信息。因此，要检查所有帖子以及您是否需要类似的特定帖子(应正确转义$login_user_id)

SELECT p.* , 
CASE 
WHEN  EXISTS (SELECT NULL FROM likes l 
  WHERE l.user_id = $login_user_id and l.post_id =p.post_id) THEN 1
ELSE 0
END as is_liked
FROM posts p