使用 SQL IN 和 SQL OR 运算符的 Rails 3 ActiveRecord 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4207936/
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 3 ActiveRecord query using both SQL IN and SQL OR operators
提问by kateray
I'm writing a Rails 3 ActiveRecord query using the "where" syntax, that uses both the SQL IN and the SQL OR operator and can't figure out how to use both of them together.
我正在使用“where”语法编写 Rails 3 ActiveRecord 查询，它同时使用 SQL IN 和 SQL OR 运算符，但不知道如何将它们一起使用。
This code works (in my User model):
此代码有效（在我的用户模型中）：
Question.where(:user_id => self.friends.ids)
#note: self.friends.ids returns an array of integers
but this code
但是这段代码
Question.where(:user_id => self.friends.ids OR :target => self.friends.usernames)
returns this error
返回此错误
syntax error, unexpected tCONSTANT, expecting ')'
...user_id => self.friends.ids OR :target => self.friends.usern...
Any idea how to write this in Rails, or just what the raw SQL query should be?
知道如何在 Rails 中编写它，或者原始 SQL 查询应该是什么？
采纳答案by jon_darkstar
raw SQL
原始 SQL
SELECT *
FROM table
WHERE user_id in (LIST OF friend.ids) OR target in (LIST OF friends.usernames)
with each list comma separate. I don't know the Rails ActiveRecord stuff that well. For AND you would just put a comma between those two conditions, but idk about OR
每个列表逗号分隔。我不太了解 Rails ActiveRecord 的东西。对于 AND，您只需在这两个条件之间加一个逗号，但不知道 OR
回答by Fábio Batista
You don't need to use raw SQL, just provide the pattern as a string, and add named parameters:
您不需要使用原始 SQL，只需将模式作为字符串提供，并添加命名参数：
Question.where('user_id in (:ids) or target in (:usernames)',
:ids => self.friends.ids, :usernames => self.friends.usernames)
Or positional parameters:
或位置参数：
Question.where('user_id in (?) or target in (?)',
self.friends.ids, self.friends.usernames)
You can also use the excellent Squeelgem, as @erroric pointed out on his answer (the my { }block is only needed if you need access to selfor instance variables):
您还可以使用出色的Squeelgem，正如@erroric 在他的回答中指出的那样（my { }仅当您需要访问self或实例变量时才需要该块）：
Question.where { user_id.in(my { self.friends.ids }) |
target.in(my { self.friends.usernames }) }
回答by Timo
Though Rails 3 AR doesn't give you an or operator you can still achieve the same result without going all the way down to SQL and use Arel directly. By that I mean that you can do it like this:
尽管 Rails 3 AR 没有提供 or 运算符，但您仍然可以达到相同的结果，而无需一直深入到 SQL 并直接使用 Arel。我的意思是你可以这样做：
t = Question.arel_table
Question.where(t[:user_id].in(self.friends.ids).or(t[:username].in(self.friends.usernames)))
Some might say it ain't so pretty, some might say it's pretty simply because it includes no SQL. Anyhow it most certainly could be prettier and there's a gem for it too: MetaWhere
有些人可能会说它不是那么漂亮，有些人可能会说它很简单，因为它不包含 SQL。无论如何，它肯定可以更漂亮，而且它也有一个宝石：MetaWhere
For more info see this railscast: http://railscasts.com/episodes/215-advanced-queries-in-rails-3and MetaWhere site: http://metautonomo.us/projects/metawhere/
有关更多信息，请参阅此 railscast：http://railscasts.com/episodes/215-advanced-queries-in-rails-3 和 MetaWhere 站点：http: //metaautomo.us/projects/metawhere/
UPDATE: Later Ryan Bates has made another railscast about metawhere and metasearch: http://railscasts.com/episodes/251-metawhere-metasearchLater though Metawhere (and search) have become more or less legacy gems. I.e. they don't even work with Rails 3.1. The author felt they (Metawhere and search) needed drastic rewrite. So much that he actually went for a new gem all together. The successor of Metawhere is Squeel. Read more about the authors announcement here: http://erniemiller.org/2011/08/31/rails-3-1-and-the-future-of-metawhere-and-metasearch/and check out the project home page: http://erniemiller.org/projects/squeel/"Metasearch 2.0" is called Ransack and you can read something about it from here: http://erniemiller.org/2011/04/01/ransack-the-library-formerly-known-as-metasearch-2-0/
更新：后来 Ryan Bates 制作了另一个关于 metawhere 和 metasearch 的 railscast：http://railscasts.com/episodes/251-metawhere-metasearch 后来虽然 Metawhere（和搜索）或多或少已经成为遗留的宝石。即他们甚至不使用 Rails 3.1。作者觉得他们（Metawhere 和搜索）需要彻底重写。以至于他实际上一起去寻找新的宝石。Metawhere 的继任者是 Squeel。在此处阅读有关作者公告的更多信息：http: //erniemiller.org/2011/08/31/rails-3-1-and-the-future-of-metawhere-and-metasearch/并查看项目主页： http://erniemiller.org/projects/squeel/“Metasearch 2.0”被称为 Ransack，你可以从这里阅读一些关于它的信息： http://erniemiller.org/2011/04/01/ransack-the-library-before-known-as-metasearch-2-0/
回答by erroric
Alternatively, you could use Squeel. To my eyes, it is simpler. You can accomplish both the IN (>>) and OR (|) operations using the following syntax:
或者，您可以使用 Squeel。在我看来，它更简单。您可以使用以下语法完成 IN ( >>) 和 OR ( |) 运算：
Question.where{(:user_id >> my{friends.id}) | (:target >> my{friends.usernames})}
I generally wrap my conditions in (...)to ensure the appropriate order of operation - both the INs happen before the OR.
我通常将我的条件包装起来(...)以确保适当的操作顺序 - 两个 IN 都发生在 OR 之前。
The my{...}block executes methods from the selfcontext as defined before the Squeel call - in this case Question. Inside of the Squeel block, selfrefers to a Squeel object and not the Questionobject (see the Squeel Readme for more). You get around this by using the my{...}wrapper to restore the original context.
该my{...}块从selfSqueel 调用之前定义的上下文中执行方法- 在这种情况下Question。在 Squeel 块内部，self指的是 Squeel 对象而不是Question对象（有关更多信息，请参阅 Squeel 自述文件）。您可以通过使用my{...}包装器来恢复原始上下文来解决这个问题。
