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')

Answer 3

The usual approach is to filter for cats OR dogs in the where clause. Then you group by on user_id, and demand that the resulting group having count(distinct pet.id) = 2.

I'm not sure how you express having in ActiveRecord; this post seems to contain a workaround.

Answer 4

for this question there are so many ways to get solution you can do as follow also...

select dog_table.user_name from 
(
    select * 
    FROM users,pets  
    where pets.user_id = users.id 
    and  pets.animal = 'dog'
) dog_table,
(
    select * 
    FROM users,pets  
    where pets.user_id = users.id 
    and  pets.animal = 'cat'
) cat_table
where dog_table.user_id = cat_table.user_id

Answer 5

This should be helpful. try this

select u.* from users u, pets p1 where u.id = p1.user_id AND p1.animal="cat" AND p1.user_id in (select user_id from pets where animal='dog')

Answer 6

I rarely answer questions on SO but I will give it a try. :)

SELECT name
FROM users
WHERE id IN (SELECT a.user_id
             FROM (
                    (SELECT user_id FROM pets WHERE animal = 'cat') a
                    INNER JOIN
                    (SELECT user_id FROM pets WHERE animal = 'dog') b
                    ON a.user_id = b.user_id
                  ));

Answer 7

There are a number of ways to do this -- some of the above will work; also, here is a slightly different approach that uses essentially 'views' -- essentially, just inheriting from your generic 'pets' class into two separate classes (cats & dogs).

SELECT
   id,
   name
FROM
      users
   INNER JOIN
      (
      SELECT DISTINCT
         user_id as belongs_to
      FROM
         pets
      WHERE
         animal = 'dog'
      ) dog
   ON users.id = dog.belongs_to
   INNER JOIN
      (
      SELECT DISTINCT
         user_id as belongs_to
      FROM
         pets
      WHERE
         animal = 'cat'
      ) cat
   ON users.id = cat.belongs_to

Answer 8

Here is another solution. Bit more Rails friendly..

User.all(:select => "DISTINCT users.*",
 :joins=>[:pets, :pets],
 :conditions => ["pets.animal = ? AND pets_users.animal = ?", "cat", "dog"])

Read this article on the relative merits of using JOIN vs GROUP BY + HAVING for such solution.

Refer to this SO question that discusses this problem in detail.

Answer 9

you can get this result various ways.

Hope it help you. try this out,

SELECT id FROM (SELECT user_id AS id FROM users
INNER JOIN pets ON pets.user_id=users.id
GROUP BY pets.user_id,pets.animal
HAVING COUNT(pets.user_id)>0 AND (pets.animal='cat' OR pets.animal='dog')
)AS s GROUP BY id HAVING COUNT(id)>1