CODEWITHSUNDEEP
mysqlsqlrelational-database
Wade Sonenberg
Wade Sonenberg

Reputation: 37

SQL Query to compare users by likes/dislikes

I'm looking for some terms, or direction on this, not somebody to write the query. But I have no idea what question to be asking, or what jargon to research.

So I have a table, let's call it reactions and it holds three columns: users, post_id, and reaction as so:

user | post_id | reaction
1    | 23      | 1
1    | 24      | 0
2    | 15      | 0
3    | 16      | 0
4    | 17      | 1
3    | 14      | 0
2    | 23      | 1
2    | 12      | 1
1    | 17      | 0

How would I go about selecting a user that has the most reactions to posts in common with a given user?

For example, if knew user #5 a reaction of "1" to post "12" and a reaction of "0" to post "15" then I would find him comparable to user #2 who reacted the same way to those two data points.

Of course this would get more complicated as more users react to more posts, I would have to sort them by how well reactions align between users.

Anyways, thank you so much in advance for reading, and any help on where to read up on things would be much appreciated!

Upvotes: 0

Views: 228

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can just count the number of reactions in common using a self-join

select r.user, count(*)
from reactions r join
     reactions r2
     on r.post_id = r2.post_id and r.reaction = r2.reaction
where r2.user = @user
group by r.user
order by count(*) desc;

Upvotes: 2

Related Questions