ActiveRecord Arel OR condition

问题:

How can you combine 2 different conditions using logical OR instead of AND?

NOTE: 2 conditions are generated as rails scopes and can't be easily changed into something like where("x or y") directly.

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

(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?

#=> select ... from ... where kind = 'admin' OR kind = 'author'

It seems (according to Arel readme):

The OR operator is not yet supported

But I hope it doesn't apply here and expect to write something like:

(admins.or authors).to_sql

回答1:

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

回答2:

ActiveRecord queries are ActiveRecord::Relation objects (which maddeningly do not support or), not Arel objects (which do).

[ UPDATE: as of Rails 5, "or" is supported in ActiveRecord::Relation; see https://stackoverflow.com/a/33248299/190135 ]

But luckily, their where method accepts ARel query objects. So if User < ActiveRecord::Base...

users = User.arel_table
query = User.where(users[:kind].eq('admin').or(users[:kind].eq('author')))

query.to_sql now shows the reassuring:

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.all to execute the actual database call.

回答3:

From the actual arel page:

The OR operator works like this:

users.where(users[:name].eq('bob').or(users[:age].lt(25)))

回答4:

As of Rails 5 we have ActiveRecord::Relation#or, allowing you to do this:

User.where(kind: :author).or(User.where(kind: :admin))

...which gets translated into the sql you'd expect:

>> puts User.where(kind: :author).or(User.where(kind: :admin)).to_sql
SELECT "users".* FROM "users" WHERE ("users"."kind" = 'author' OR "users"."kind" = 'admin')

回答5:

I've hit the same problem looking for an activerecord alternative to mongoid's #any_of.

@jswanner answer is good, but will only work if the where parameters are a 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_of implementation I've made : 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.

回答6:

Just make a scope for your OR condition:

scope :author_or_admin, where(['kind = ? OR kind = ?', 'Author', 'Admin'])

回答7:

Using SmartTuple it's going to look something like this:

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 clear and convenient than method chaining in this particular case.

回答8:

To extend jswanner answer (which is actually awesome solution and helped me) for googling people:

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

回答9:

What about this approach: http://guides.rubyonrails.org/active_record_querying.html#hash-conditions (and check 2.3.3)

admins_or_authors = User.where(:kind => [:admin, :author])

回答10:

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

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:

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.

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.