mysql查询以检查用户是否投票

Question

在这种情况下我有

帖子表:

id 
title
description

post_votes 表:

id
post_id
user_id

评论表:

id
post_id
user_id
content

用户表:

id
username

我查询我的索引页面(登录用户)

select 
`posts`.`id`, 
`posts`.`title`, 
`posts`.`description`,  
COUNT(distinct post_votes.id) AS votes, 
COUNT(distinct comments.id) AS comments 
from `posts` 
left join `post_votes` on `post_votes`.`post_id` = `posts`.`id` 
left join `comments` on `comments`.`post_id` = `posts`.`id` 
group by `posts`.`id`

这显示了所有帖子(事件)。对于每一个显示总票数和评论数。此外，在索引页面上为每个帖子显示一个投票按钮。

用户可以对一篇文章进行投票，我希望能够检查用户是否已经对每个帖子进行了投票，在这种情况下，显示禁用了该帖子的投票按钮。

我有用户 ID。

我该怎么做？

Answer 1

不确定 mysql 格式，但像这样的东西应该可以工作:

select 
CASE WHEN userVote.post_id IS NULL THEN 'No user vote' ELSE 'User has voted' END AS 'VoteStatus'
`posts`.`id`, 
`posts`.`title`, 
`posts`.`description`,  
COUNT(distinct post_votes.id) AS votes, 
COUNT(distinct comments.id) AS comments 
from `posts` 
left join `post_votes` on `post_votes`.`post_id` = `posts`.`id`
left join `post_votes` AS userVote on `post_votes`.`post_id` = `posts`.`id` AND `post_votes`.`user_id` = @myUserID
left join `comments` on `comments`.`post_id` = `posts`.`id` 
group by `posts`.`id`