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

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.

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

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

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.

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

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

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

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

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.

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