mysql query to check if user voted

Question

in this case i have

posts table :

id 
title
description

post_votes table:

id
post_id
user_id

comments table:

id
post_id
user_id
content

users table:

id
username

I have a query for my index page (logged users)

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`

This shows all posts (events). For each one shows the total votes and the number of comments. In addition show a vote button for each post on the index page.

Users can vote an article and I would like to be able to check if the user already voted each post, and in this case show disabled the vote button for this post.

I have the user id.

How can i do it?

Answer 1

Not sure about the mysql format but something like this should work:

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`