带查找功能的 Rails SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/985211/
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 SQL Query with find
提问by Nave
I want this SQL query to be written in rails controller using find:
我希望使用 find 在 rails 控制器中编写此 SQL 查询：
select id,name from questions
where id not in (select question_id from levels_questions where level_id=15)
How will I do this? I am using Rails framework and MySQL. Thanks in advance.
我将如何做到这一点？我正在使用 Rails 框架和 MySQL。提前致谢。
回答by joshng
Question.find_all_by_sql('select id,name from questions where id not in (select question_id from levels_questions where level_id=15)')
This is admittedly non-ActiveRecord-ish, but I find that complicated queries such as this tend to be LESS clear/efficient when using the AR macros. If you already have the SQL constructed, you might as well use it.
这无疑是非 ActiveRecord-ish，但我发现使用 AR 宏时，诸如此类的复杂查询往往不太清晰/效率较低。如果您已经构建了 SQL，您不妨使用它。
Some suggestions: encapsulate this find call in a method INSIDE the Question class to hide the details from the controller/view, and consider other SQL constructions that may be more efficient (eg, an OUTER JOIN where levels_questions.question_id is null)
一些建议：将这个 find 调用封装在 Question 类中的一个方法中以隐藏控制器/视图中的细节，并考虑其他可能更有效的 SQL 结构（例如，一个 OUTER JOIN 其中 levels_questions.question_id 为空）
回答by Ryan Oberoi
Simple way:
简单的方法：
ids = LevelsQuestion.all(:select => "question_id",
:conditions => "level_id = 15").collect(&:question_id)
Question.all(:select => "id, name", :conditions => ["id not in (?)", ids])
One shot:
一枪：
Question.all(:select => "id, name",
:conditions => ["id not in (select question_id from levels_questions where level_id=15)"])
回答by Vikko
And the rails 3 way:
和导轨 3 方式：
ids = LevelsQuestion.select(:question_id).where(:level_id => 15).collect(&:question_id)
Question.select(:id, :name).where("id not in (?)", ids)
