CODEWITHSUNDEEP
mysqlcount
pedalpete
pedalpete

Reputation: 21556

Check for a unique value within a count, but get all results

I'm trying to create a single query which, similar to stack overflow, will give me the number of votes, but also make sure that the currently viewing user can't upvote again if they've already upvoted.

my query currently looks like

SELECT cid, text, COUNT(votes.parentid)  FROM comments LEFT JOIN votes ON comments.cid=votes.parentid AND votes.type=3 WHERE comments.type=0 AND comments.parentid='$commentParentid' GROUP BY comments.cid

But I'm completely stumpted on how to add the check to see if the userid is in the votes table.

The other option is to add a seperate query where

SELECT COUNT(*) FROM votes WHERE userid='$userid' AND parentid='$commentParentid' AND type=3

I'm just realizing I'm so lost with this that I don't even really know what tags to provide.

Upvotes: 0

Views: 73

Answers (1)

Doctor Kicks
Doctor Kicks

Reputation: 494

SELECT cid, text, COUNT(votes.parentid), 
    COUNT(IF(votes.userid='$userid',1,NULL)) = 0 AS can_vote
FROM comments 
LEFT JOIN votes ON comments.cid=votes.parentid AND votes.type=3 
WHERE comments.type=0 AND comments.parentid='$commentParentid' 
GROUP BY comments.cid

This should give you a flag can_vote based on the number of times the user has voted on the comment, if you insist on doing this in SQL.

Upvotes: 1

Related Questions