CODEWITHSUNDEEP
mysql
Mustafa
Mustafa

Reputation: 162

Count likes of each user using two tables

I have two tables, the first one is POSTSthat has three fields (post_id, user_id, text) the second table is LIKES has two fields (user_id, post_id) likes.user_id is the id of the user who liked the post.

Now I want to query the database and get the count of likes (distance) of each user to another. Meaning I'd get a result of three fields, four with the index (I'm not too worried about it for now):

User 1 | User 2 | count(INT) | index
-------------------------------------
User_a | User_b |     45     | ab
User_c | User_n |     53     | cn
User_b | User_a |     38     | ba

I tried using something like this, but I'm not always getting an accurate number of likes. Also, I'm getting some data redundancy.

SELECT likes.user_id, posts.user_id,
COUNT(likes.post_id)
from posts, likes
WHERE posts.post_id = likes.post_id
GROUP BY posts.user_id
ORDER BY COUNT(likes.post_id)

I'm a MySQL rookie, so excuse any errors!, Thank you for any help!

Upvotes: 0

Views: 661

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

SELECT likes.user_id, posts.user_id,
COUNT(DISTINCT likes.post_id) --<<changed
from posts
INNER JOIN likes ON posts.post_id = likes.post_id --<<changed
GROUP BY likes.user_id, posts.user_id --<<changed
ORDER BY COUNT(DISTINCT likes.post_id) --<<changed

I suspect the "data redundancy" may be coming from your group by clause. While MySQL does allow you to write queries that don't comply with SQL standards in this clause, it is important to always specify all the non-aggregating columns. (see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html for details of the non-standard syntax MySQL allows, but which should not be used.)

The other item of change is use of DISTINCT within the count function. Without access to your tables I don't know if that is required but it could address the accuracy issue you state.

Finally. Please stop using a commas separated list of tables as your from clause. This is no longer good practice and you should follow ANSI style join syntax which is more specific and easier to maintain.

Upvotes: 1

Related Questions