Rails: join 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 query all the users with a specific interests, that's fairly simple doing

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

But how can I look for users who have multiple interests? Of course if I do

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

I get always an empty result, since after the join, no row can have simultaneously interest.id = an_interest and interest.id = another_interest.

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

update (solution) that's the first working version I came up, kudos to Omar Qureshi

    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 (?) is no good - it's an OR like expression

what you will need to do is have multiple joins written out longhanded

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 change the main_join_clause to suit your needs.

Answer 2

This will get users that have at least one of the specified interests.

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

To get users that have both of the specified interests I'd 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 amazingly efficient, but it should do what you need?