Rails: combining with multiple conditions

Question

I have a simple model like

class Interest < ActiveRecord::Base has_and_belongs_to_many :user_profiles end class UserProfile < ActiveRecord::Base has_and_belongs_to_many :interests end 

When I want to request all users with specific interests, this is a fairly simple implementation

 UserProfile.joins(:interests).where('interests.id = ?', an_interest) 

But how can I search for users who have several interests? Of course, if I do

 UserProfile.joins(:interests).where('interests.id = ?', an_interest).where('interests.id = ?', another_interest) 

I always get an empty result, because after the merge, no line can have both interest.id = an_interest and interest.id = another_interest.

Is there a way in ActiveRecord to express "I need a list of users who have 2 (specified) interests related?

update (solution) that the first working version I came up with is honored by Omar Kureshi

  specified_interests.each_with_index do |i, idx| main_join_clause = "interests_#{idx}.user_profile_id = user_profiles.id" join_clause = sanitize_sql_array ["inner join interests_user_profiles interests_#{idx} on (#{main_join_clause} and interests_#{idx}.interest_id = ?)", i] relation = relation.joins(join_clause) end 

Answer 1

in (?) not good - this is OR as an expression

what you need to do is to have several long-arm combinations

 profiles = UserProfile interest_ids.each_with_index do |i, idx| main_join_clause = "interests_#{idx}.user_profile_id = user_profiles.id" join_clause = sanitize_sql_array ["inner join interests interests_#{idx} on (#{main_join_clause} and interests_#{idx}.id = ?)", i] profiles = profiles.join(join_clause) end profiles 

You may need to modify main_join_clause to suit your needs.

Answer 2

This will allow users who have at least one of these interests.

 UserProfile.joins(:interests).where(:id => [an_interest_id, another_interest_id]) 

To users who have both of these interests, I would probably do something like this:

 def self.all_with_interests(interest_1, interest_2) users_1 = UserProfile.where("interests.id = ?", interest_1.id) users_2 = UserProfile.where("interests.id = ?", interest_2.id) users_1 & users_2 end 

Not surprisingly effective, but should it do what you need?

Answer 3

Try IN (?) And an array:

 UserProfile.joins(:interests).where('interests.id IN (?)', [1,2,3,4,5])