Ruby-on-rails Rails 活动记录查询与“存在”的关联
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18234602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails active record querying association with 'exists'
提问by Lee
I am working on an app that allows Members to take a survey (Member has a one to many relationship with Response). Response holds the member_id, question_id, and their answer.
我正在开发一个允许会员进行调查的应用程序（会员与 Response 有一对多的关系）。Response 包含 member_id、question_id 和他们的答案。
The survey is submitted all or nothing, so if there are any records in the Response table for that Member they have completed the survey.
调查要么全部提交，要么不提交，因此如果该成员的响应表中有任何记录，则他们已完成调查。
My question is, how do I re-write the query below so that it actually works? In SQL this would be a prime candidate for the EXISTS keyword.
我的问题是，如何重写下面的查询以使其实际工作？在 SQL 中，这将是 EXISTS 关键字的主要候选者。
def surveys_completed
members.where(responses: !nil ).count
end
回答by MrYoshiji
You can use includesand then test if the related response(s) exists like this:
您可以使用includes然后测试相关响应是否存在，如下所示：
def surveys_completed
members.includes(:responses).where('responses.id IS NOT NULL')
end
Here is an alternative, with joins:
这是一个替代方案，包括joins：
def surveys_completed
members.joins(:responses)
end
The solution using Rails 4:
使用 Rails 4 的解决方案：
def surveys_completed
members.includes(:responses).where.not(responses: { id: nil })
end
Alternative solution using activerecord_where_assoc:
This gem does exactly what is asked here: use EXISTSto to do a condition.
It works with Rails 4.1 to the most recent.
使用的替代解决方案activerecord_where_assoc：此 gem 完全按照此处的要求执行：用于EXISTS执行条件。它适用于最新的 Rails 4.1。
members.where_assoc_exists(:responses)
It can also do much more!
它还可以做得更多！
Similar questions:
类似问题：
回答by EugZol
You can use SQL EXISTSkeyword in elegant Rails-ish manner using Where Existsgem:
您可以使用Where ExistsgemEXISTS以优雅的 Rails-ish 方式使用 SQL关键字：
members.where_exists(:responses).count
Of course you can use raw SQL as well:
当然，您也可以使用原始 SQL：
members.where("EXISTS" \
"(SELECT 1 FROM responses WHERE responses.member_id = members.id)").
count
回答by Markus
You can also use a subquery:
您还可以使用子查询：
members.where(id: Response.select(:member_id))
In comparison to something with includesit will not load the associated models (which is a performance benefit if you do not need them).
与includes它相比，它不会加载关联的模型（如果您不需要它们，这是一个性能优势）。
回答by Johnathan Ludwig
If you are on Rails 5 and above you should use left_joins. Otherwise a manual "LEFT OUTER JOINS" will also work. This is more performant than using includesmentioned in https://stackoverflow.com/a/18234998/3788753. includeswill attempt to load the related objects into memory, whereas left_joinswill build a "LEFT OUTER JOINS" query.
如果您使用的是 Rails 5 及更高版本，则应使用left_joins. 否则，手动“LEFT OUTER JOINS”也将起作用。这比https://stackoverflow.com/a/18234998/3788753 中includes提到的使用性能更高。将尝试将相关对象加载到内存中，而将构建“LEFT OUTER JOINS”查询。includesleft_joins
def surveys_completed
members.left_joins(:responses).where.not(responses: { id: nil })
end
Even if there are no related records (like the query above where you are finding by nil) includesstill uses more memory. In my testing I found includes uses ~33x more memory on Rails 5.2.1. On Rails 4.2.x it was ~44x more memory compared to doing the joins manually.
即使没有相关记录（如上面的查询，您通过 nil 查找）includes仍然使用更多内存。在我的测试中，我发现包括在 Rails 5.2.1 上使用了大约 33 倍的内存。在 Rails 4.2.x 上，与手动连接相比，内存增加了大约 44 倍。
See this gist for the test: https://gist.github.com/johnathanludwig/96fc33fc135ee558e0f09fb23a8cf3f1
请参阅此测试要点：https: //gist.github.com/johnathanludwig/96fc33fc135ee558e0f09fb23a8cf3f1
