CODEWITHSUNDEEP
laravellaravel-4
deno
deno

Reputation: 69

Select Users with Matching Interests

i have a user(id, name) Model, interest(id, interest) Model and a join table users_interests(user_id, interest_id) for a many to many relationship.

how to select all users that have for example 2 matching interests.

lets say there are 4 users(mike, andre, julian, john). 2 of them have 3 matching interests. for example mike(music, sports, games), andre(music, sports, games). the other 2 users have different interests.

i need a query where i can say give me all users with 2 matching interests. 2 is variable. the result would be mike and andre.

Upvotes: 2

Views: 140

Answers (1)

peterm
peterm

Reputation: 92805

IMHO you won't be able to build this type of query fluently, so you'd have to opt-out to raw SQL.

That being said here is one way you can approach this with SQL 

SELECT DISTINCT u.* 
  FROM
(
    SELECT t1.user_id id1, t2.user_id id2, COUNT(*) count
      FROM interest_user t1 JOIN interest_user t2
        ON t1.user_id < t2.user_id
       AND t1.interest_id = t2.interest_id 
     GROUP BY t1.user_id, t2.user_id
) q JOIN users u
    ON q.count >= 2 -- change 2 to how many mutual interests you want to have 
   AND u.id IN(id1, id2);

Here is SQLFiddle demo

Upvotes: 1

Related Questions