How do I find the user that has both a cat and a dog?

Question

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.

Answer 1

Andomar - Unfortunately, writing the query like that will not necessarily always work as desired. Specifically, having 2 cats will cause the user to show up, and having 3 pets - say, 2 cats and a dog - will cause them to be excluded.
I don't know much about ActiveRecord, but the following is standard SQL syntax that would work:

SELECT users.id
FROM Users
JOIN (SELECT user_id 
      FROM Pets
      WHERE animal IN ('dog', 'cat')
      GROUP BY user_id
      HAVING COUNT(DISTINCT animal)) Pets
  ON Pets.user_id = Users.id

This works differently than existing versions by counting the distinct "type" of pet ('cat' versus 'dog').

Answer 2

Use sub-selects to constrain the results:

User.joins(:pets).where(
  'id IN (SELECT user_id FROM pets WHERE animal = ?) AND
   id IN (SELECT user_id FROM pets WHERE animal = ?)',
  'cat', 'dog')