如何找到同时具有一只猫和一只狗的用户？

问题描述

我想做的事情在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).

我尝试这个迄今

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.

推荐答案

使用子查询来约束结果：

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

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