如何找到同时拥有猫和狗的用户? [英] How do I find the user that has both a cat and a dog?

问题描述

我想在具有多对一关系的 2 个表中进行搜索，例如

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

我想要做的是创建一个活动记录查询，该查询将返回一个同时拥有猫和狗的用户(即用户 1 - Bob).

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.

---编辑---

我刚刚发现了 Squeel，所以只是在这个问题上加了一点尾声.这允许您像这样构建子查询；

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.

推荐答案

使用子选择约束结果:

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

这篇关于如何找到同时拥有猫和狗的用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！