MySQL COUNT function not performing as I would like in multiple joined query
I have been altering a query for a while now, reading many posts on this wonderful site to get to where I am with this so far. But, alas, now I am stuck :(
This query and relevant part of the database I have designed is similar to the youtube comment liking system. Relevant tables and fields are:
USRS
usr_id int (PK)COMMENTS
comment_id int (PK) usr_id int (FK) references usrs(usr_id) topic_id int (FK) references topics(topic_id) descr varchar created varcharCOMMENT_LIKERS
comment_id int (PK) (FK) references comments(comment_id) usr_id int (PK) (FK) references usrs(usr_id) liker tinyintI want to be able to select all relevant data in one query. Aside from general data for each comment, I want to count all likes and dislikes for each comment. The query I have thus far is counting the likes for ALL comments and not for each even though I have the LEFT JOIN with ON clause: comments.comment_id = comment_likers.comment_id.
I am learning MySql and PHP so go easy with me if I have done something silly. I assure you I have looked all around for clues to the answer.
Here is the query:
SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name,
COUNT(if(comment_likers.liker = 1, 1, null)),
COUNT(if(comment_l开发者_高级运维ikers.liker = 0, 1, null)),
comment_likers2.liker
FROM comments
INNER JOIN usrs ON ( comments.usr_id = usrs.usr_id )
LEFT JOIN comment_likers ON ( comments.comment_id = comment_likers.comment_id )
LEFT JOIN comment_likers AS comment_likers2 ON ( comments.comment_id = comment_likers.comment_id AND comment_likers.usr_id = $usrID )
WHERE comments.topic_id = $tpcID
GROUP BY comments.comment_id
ORDER BY comments.created DESC
Thanks in advance
SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name,
(SELECT COUNT(*) FROM comment_likers WHERE comment_id=comments.comment_id AND liker=1)likes,
(SELECT COUNT(*) FROM comment_likers WHERE comment_id=comments.comment_id AND liker=0)dislikes
liker
FROM comments
INNER JOIN usrs ON ( comments.usr_id = usrs.usr_id )
LEFT JOIN comment_likers ON ( comments.comment_id = comment_likers.comment_id
AND comment_likers.usr_id = $usrID )
WHERE comments.topic_id=$tpcID
ORDER BY comments.created DESC;
A couple notes. I wasn't too sure what the second left join on comment_likers was supposed to accomplish (the one using $usrID). Are you only interested in likes from on a specific topic from a specific user?
Also, you might think about changing the schema for comments created
to be a datetime instead of a varchar.
精彩评论