SQL ActiveRecord Arel OR 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7976358/
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
ActiveRecord Arel OR condition
提问by Dmytrii Nagirniak
How can you combine 2 different conditions using logical OR instead of AND?
如何使用逻辑 OR 而不是 AND 来组合 2 个不同的条件？
NOTE:2 conditions are generated as rails scopes and can't be easily changed into something like where("x or y")directly.
注意：2 个条件是作为 rails 范围生成的，不能轻易地where("x or y")直接更改为类似的内容。
Simple example:
简单的例子：
admins = User.where(:kind => :admin)
authors = User.where(:kind => :author)
It's easy to apply AND condition (which for this particular case is meaningless):
很容易应用 AND 条件（对于这种特殊情况是没有意义的）：
(admins.merge authors).to_sql
#=> select ... from ... where kind = 'admin' AND kind = 'author'
But how can you produce the following query having 2 different Arel relations already available?
但是如何生成以下具有 2 个不同 Arel 关系可用的查询？
#=> select ... from ... where kind = 'admin' OR kind = 'author'
It seems (according to Arel readme):
似乎（根据 Arel 自述文件）：
The OR operator is not yet supported
尚不支持 OR 运算符
But I hope it doesn't apply here and expect to write something like:
但我希望它在这里不适用，并希望写出类似的东西：
(admins.or authors).to_sql
回答by AlexChaffee
ActiveRecord queries are ActiveRecord::Relationobjects (which maddeningly do not support or), not Arel objects (which do).
ActiveRecord 查询是ActiveRecord::Relation对象（非常不支持or），而不是 Arel 对象（支持）。
[ UPDATE: as of Rails 5, "or" is supported in ActiveRecord::Relation; see https://stackoverflow.com/a/33248299/190135]
[更新：从 Rails 5 开始，“或”被支持ActiveRecord::Relation；见https://stackoverflow.com/a/33248299/190135]
But luckily, their wheremethod accepts ARel query objects. So if User < ActiveRecord::Base...
但幸运的是，他们的where方法接受 ARel 查询对象。所以如果User < ActiveRecord::Base...
users = User.arel_table
query = User.where(users[:kind].eq('admin').or(users[:kind].eq('author')))
query.to_sqlnow shows the reassuring:
query.to_sql现在显示令人放心：
SELECT "users".* FROM "users" WHERE (("users"."kind" = 'admin' OR "users"."kind" = 'author'))
For clarity, you could extract some temporary partial-query variables:
为清楚起见，您可以提取一些临时部分查询变量：
users = User.arel_table
admin = users[:kind].eq('admin')
author = users[:kind].eq('author')
query = User.where(admin.or(author))
And naturally, once you have the query you can use query.allto execute the actual database call.
很自然地，一旦有了查询，就可以query.all用来执行实际的数据库调用。
回答by jswanner
I'm a little late to the party, but here's the best suggestion I could come up with:
我参加聚会有点晚了，但这是我能想出的最佳建议：
admins = User.where(:kind => :admin)
authors = User.where(:kind => :author)
admins = admins.where_values.reduce(:and)
authors = authors.where_values.reduce(:and)
User.where(admins.or(authors)).to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE ((\"users\".\"kind\" = 'admin' OR \"users\".\"kind\" = 'author'))"
回答by pje
As of Rails 5 we have ActiveRecord::Relation#or, allowing you to do this:
从 Rails 5 开始，我们有ActiveRecord::Relation#or，允许您这样做：
User.where(kind: :author).or(User.where(kind: :admin))
...which gets translated into the sql you'd expect:
...它被翻译成你期望的 sql：
>> puts User.where(kind: :author).or(User.where(kind: :admin)).to_sql
SELECT "users".* FROM "users" WHERE ("users"."kind" = 'author' OR "users"."kind" = 'admin')
回答by Dave Newton
From the actual arel page:
The OR operator works like this:
users.where(users[:name].eq('bob').or(users[:age].lt(25)))
OR 运算符的工作方式如下：
users.where(users[:name].eq('bob').or(users[:age].lt(25)))
回答by kik
I've hit the same problem looking for an activerecord alternative to mongoid's #any_of.
我在寻找 mongoid 的 activerecord 替代品时遇到了同样的问题#any_of。
@jswanner answer is good, but will only work if the where parameters are a Hash :
@jswanner 答案很好，但只有在 where 参数是 Hash 时才有效：
> User.where( email: 'foo', first_name: 'bar' ).where_values.reduce( :and ).method( :or )
=> #<Method: Arel::Nodes::And(Arel::Nodes::Node)#or>
> User.where( "email = 'foo' and first_name = 'bar'" ).where_values.reduce( :and ).method( :or )
NameError: undefined method `or' for class `String'
To be able to use both strings and hashes, you can use this :
为了能够同时使用字符串和哈希，你可以使用这个：
q1 = User.where( "email = 'foo'" )
q2 = User.where( email: 'bar' )
User.where( q1.arel.constraints.reduce( :and ).or( q2.arel.constraints.reduce( :and ) ) )
Indeed, that's ugly, and you don't want to use that on a daily basis. Here is some #any_ofimplementation I've made : https://gist.github.com/oelmekki/5396826
确实，这很丑陋，您不想每天都使用它。这是#any_of我所做的一些实现：https: //gist.github.com/oelmekki/5396826
It let do that :
它让这样做：
> q1 = User.where( email: 'foo1' ); true
=> true
> q2 = User.where( "email = 'bar1'" ); true
=> true
> User.any_of( q1, q2, { email: 'foo2' }, "email = 'bar2'" )
User Load (1.2ms) SELECT "users".* FROM "users" WHERE (((("users"."email" = 'foo1' OR (email = 'bar1')) OR "users"."email" = 'foo2') OR (email = 'bar2')))
Edit : since then, I've published a gem to help building OR queries.
编辑：从那时起，我发布了一个 gem 来帮助构建 OR 查询。
回答by Unixmonkey
Just make a scope for your OR condition:
只需为您的 OR 条件确定一个范围：
scope :author_or_admin, where(['kind = ? OR kind = ?', 'Author', 'Admin'])
回答by Alex Fortuna
Using SmartTupleit's going to look something like this:
使用SmartTuple它将看起来像这样：
tup = SmartTuple.new(" OR ")
tup << {:kind => "admin"}
tup << {:kind => "author"}
User.where(tup.compile)
OR
或者
User.where((SmartTuple.new(" OR ") + {:kind => "admin"} + {:kind => "author"}).compile)
You may think I'm biased, but I still consider traditional data structure operations being far more clearand convenient than method chaining in this particular case.
你可能认为我有偏见，但我仍然认为在这种特殊情况下，传统的数据结构操作比方法链更清晰和方便。
回答by equivalent8
To extend jswanner answer(which is actually awesome solution and helped me) for googling people:
为谷歌搜索的人扩展jswanner 答案（这实际上是很棒的解决方案并帮助了我）：
you can apply scope like this
你可以像这样应用范围
scope :with_owner_ids_or_global, lambda{ |owner_class, *ids|
with_ids = where(owner_id: ids.flatten).where_values.reduce(:and)
with_glob = where(owner_id: nil).where_values.reduce(:and)
where(owner_type: owner_class.model_name).where(with_ids.or( with_glob ))
}
User.with_owner_ids_or_global(Developer, 1, 2)
# => ...WHERE `users`.`owner_type` = 'Developer' AND ((`users`.`owner_id` IN (1, 2) OR `users`.`owner_id` IS NULL))
回答by Sjors Branderhorst
What about this approach: http://guides.rubyonrails.org/active_record_querying.html#hash-conditions(and check 2.3.3)
这种方法怎么样：http: //guides.rubyonrails.org/active_record_querying.html#hash-conditions（并检查2.3.3）
admins_or_authors = User.where(:kind => [:admin, :author])
回答by Dmytrii Nagirniak
Unfortunately it is not supported natively, so we need to hack here.
不幸的是，它本身并不支持，所以我们需要在这里破解。
And the hack looks like this, which is pretty inefficient SQL (hope DBAs are not looking at it :-) ):
hack 看起来像这样，这是非常低效的 SQL（希望 DBA 不会看它:-)）：
admins = User.where(:kind => :admin)
authors = User.where(:kind => :author)
both = User.where("users.id in (#{admins.select(:id)}) OR users.id in (#{authors.select(:id)})")
both.to_sql # => where users.id in (select id from...) OR users.id in (select id from)
This generates subselets.
这会生成子集。
And a little better hack (from SQL perspective) looks like this:
更好的 hack（从 SQL 的角度）看起来像这样：
admins_sql = admins.arel.where_sql.sub(/^WHERE/i,'')
authors_sql = authors.arel.where_sql.sub(/^WHERE/i,'')
both = User.where("(#{admins_sql}) OR (#{authors_sql})")
both.to_sql # => where <admins where conditions> OR <authors where conditions>
This generates proper OR condition, but obviously it only takes into account the WHERE part of the scopes.
这会生成适当的 OR 条件，但显然它只考虑了范围的 WHERE 部分。
I chose the 1st one until I'll see how it performs.
我选择了第一个，直到我看到它的表现如何。
In any case, you must be pretty careful with it and watch the SQL generated.
在任何情况下，您都必须非常小心并观察生成的 SQL。
