I want to do a search across 2 tables that have a many-to-one relationship, eg
class User << ActiveRecord::Base
has_many :pets
end
class Pet << ActiveRecord::Base
belongs_to :users
end
Now let's say I have some data like so
users
id name
1 Bob
2 Joe
3 Brian
pets
id user_id animal
1 1 cat
2 1 dog
3 2 cat
4 3 dog
What I want to do is create an active record query that will return a user that has both a cat and a dog (i.e. user 1 - Bob).
My attempt at this so far is
User.joins(:pets).where('pets.animal = ? AND pets.animal = ?','dog','cat')
Now I understand why this doesn't work - it's looking for a pet that is both a dog and a cat so returns nothing. I don't know how to modify this to give me the answer I want however. Does anyone have any suggestions? This seems like it should be easy - it doesn't seem like an especially unusual situation.
---edit---
Just adding a little coda to this question as I have just discovered Squeel. This allows you to build a subquery like so;
User.where{id.in(Pet.where{animal == 'Cat'}.select{user_id} & id.in(Pet.where{animal == 'Dog'}.select{user_id}))
This is what will find its way into my app.