I am creating some sort of a social media like facebook and I'm trying to get the number of likes a post has using the following query.
SELECT posts.object_ID, posts.user_ID, posts.datetime, posts.text,
COUNT(likes.object_ID) AS likes,
SUM(IF(likes.user_ID=?, 1, 0)) AS allowLike, users.first_name,
users.last_name, userinfo.image_ID
FROM posts
LEFT JOIN users ON users.ID=posts.user_ID
LEFT JOIN friends ON (friends.user_ID_1=? OR friends.user_ID_2=?)
AND friends.approved=1
LEFT JOIN userinfo ON users.ID=userinfo.user_ID
LEFT JOIN likes ON likes.object_ID=posts.object_ID
WHERE ". $str ."
GROUP BY posts.object_ID
ORDER BY posts.datetime DESC
LIMIT 0,30
The value of $str is just a mechanism to filter out friends and stuff, e.g.:
$str = "(posts.user_ID=friends.user_ID_1 OR posts.user_ID=friends.user_ID_2) OR posts.user_ID=? ";
What happens now is that COUNT(likes.object_ID) returns 4 and SUM(IF(likes.user_ID=?, 1, 0)) returns 4 as well.
This, surprisingly enough, is the actual amount of likes times the number of friends I have(4), I assume, since a post with 2 likes shows up as 8 likes. Yesterday I had 3 friends and it showed up as 3. And it's only happening when the post is my own.
I use a very similar query for fetching the comments, yet nothing strange is happening there.
Any ideas?
EDIT: It is not just limited to my own posts. I just found a case where it is in someone elses post. Still, it is NOT happening to every post... which is weird. Also, it is not bound to my own likes...
EDIT2: Upon 'reliking' the post I was speaking about in EDIT1, it showed up as one, and after a couple of refreshes it still showed up as 1 like. Weird..
Well, I decided to give up on this, and just go for a solution similar to the one described by myself and @Duniyadnd in the comments on my question.
I have a table objects. It only contained an ID. I added a field 'likes'. And created two triggers: One for the newLike, and one for a deletedLike which would update the likes column at the given object_ID.
Now my query is as such:
SELECT
posts.object_ID, posts.user_ID, posts.datetime, posts.text, objects.likes,
SUM(IF(likes.user_ID=?, 1, 0)) AS allowLike, users.first_name, users.last_name,
userinfo.image_ID
FROM posts
LEFT JOIN users ON users.ID=posts.user_ID
LEFT JOIN friends ON (friends.user_ID_1=? OR friends.user_ID_2=?) AND friends.approved=1
LEFT JOIN userinfo ON users.ID=userinfo.user_ID
LEFT JOIN objects ON objects.ID=posts.object_ID
LEFT JOIN likes ON likes.object_ID=posts.object_ID
WHERE ". $str ."
GROUP BY posts.object_ID ORDER BY posts.datetime DESC LIMIT 0,30
So I cleared my likes table to start over, yet, allowLike (SUM(IF(likes.user_ID=?, 1, 0))) still returns 4. There is only 1 entry in the likes table, I'm still curious why this is hapenning? Again, I have 4 friends.
Or would I better place this in a new question.
Your problem may be related to your WHERE clause. A parameter is not parsed; your WHERE is simply a long varchar, your RDMS is interpreting this as TRUE.
The joins are causing multiple intermediate rows to be created from the cross joins of the rows in one table with those of another table. Therefore your 4 friends are causing the 1 like to be turned into 4 intermediate rows, and then it is summed up to get a result of 4.
Instead of all the joins, try using a select within the first select, like this query on posts/comments/likes:
select postid, postmessage,
(select count(commentid) from comment where post.postid = comment.postid) as numcomments,
(select sum(value) from like where post.postid = like.postid) as popularity
from post
group by postid
(I have like.value as being 1 or -1 as up votes and down votes)
Hope that helps :)