ActiveRecord Arel OR condition

Question

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

Howund

May be of help: ActiveRecord OR query Hash notation – Electrophorus

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

Parlance

That's pretty nice actually. Thanks. – Howund

This was giving me an "undefined method .or for [string]" error, but "#{admins} or #{authors}" worked great. – Inharmonious

bhaibel: I had the same problem - it was caused by join. I use the joined model instead and no more string error. – Adz

you can skip the "where_values.reduce" step if you start with a real ARel query... see my answer – Aphanite

This doesn't work as expected (rails 3.2.12) if your scopes have more than one condition. The problem is that parens are not placed around the OR conditions, causing ANDs to affect the entire query instead of part – Passageway

Answer 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://mcmap.net/q/145782/-activerecord-arel-or-condition ]

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.

Aphanite

This worked for me too. Now that Rails 4 is released, is this still the best way to get the OR condition? – Monitory

Answer 3

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

Crutchfield

This works well to get multiple ids baased on different searches on a complex table including Postgesql where 'LIKE' does not work on integers. – Zeidman

Answer 4

From the actual arel page:

The OR operator works like this:

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

Taxiway

I saw that. It is NOT yet supported. How does it answer the question? – Howund

Couldn't format it in a comment. There's tests for the OR operators, but the page you linked to is from 2009 and isn't the AREL actually being used. It may not answer the question, but at least it's the correct reference, and doesn't say it isn't supported. – Taxiway

Ok. But you can't do it with Rails ActiveRecord scopes. Have you tried the example with admins & authors or similar? There's no or method on ActiveRecord::Relation. Converting it to Arel gives another set of problems (the query is SelectManager, not Where). Or I have missed something? – Howund

Oh, thought you were referring to arel because you linked to it--sorry. – Taxiway

AFAIK, you can pass arel conditions to AR methods, so this should work: User.where(users[:name].eq('bob').or(users[:age].lt(25))) – Vanesavanessa

@tokland, but how do you combine two existing realtions with OR? – Howund

@Dmytrii: AFAIK this is not possible, ORs must be in the same expression. For me it makes sense, scopes are accumulative, so AND is ok while OR is not. – Vanesavanessa

Answer 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.

Cale

Answer 6

Just make a scope for your OR condition:

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

Dieter

Well, your query isn't correct SQL :) But what you suggested is exactly what I cannot do. Please DO read the question. Now, do you see the note in bold? – Howund

@DmytriiNagirniak Fixed the sql error, but I still don't see why this wouldn't work for you. Maybe its an Arel thing (I still use 2.3 mostly), or maybe the question needs more clarification. – Dieter

This would work. But as I said in my question, I have 2 scopes. I need to combine those using OR statement. I can't rewrite those 2 scopes into a single where statement. – Howund

Answer 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.

Miller

Can you convert the scopes into SmartTuple easily? I am asking because the app is already using heavily the Arel. – Howund

No, but you may return SmartTuple objects from your code instead of returning scopes, and then quickly convert SmartTuples into scopes when needed. – Miller

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

Upon

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

Bede

Please read the question. There are 2 rails relations generated that can't be controlled. – Howund

True, I should have read the question better. You specifically state: "But how can you produce the following query having 2 different Arel relations already available?", and I do not respond to that. And indeed, when "OR"-ing condition scopes isn't supported you need to resort to hacks. So an apology is in order. I just wanted to point out there might be an easier solution that would help you skip having the problem in the first place. – Bede

Answer 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.

Howund

Honestly, it works, but composing sql with regexes like that, I just had to down vote your answer. Then you might as well just write sql and use find_by_sql and skip the whole Arel layer. – Bede

Mind to show better solutions then. I provided 2, one of which I don't like and don't use. – Howund

Three people have given valid answers using valid ARel and yet you've accepted your own ugly answer where, as Sjors said, you may as well have just manipulated strings to construct SQL in the first place. Downvote. – Chaulmoogra

To be really honest, Arel refuses to compile queries if you have joins on any side of the OR, referring to the "OR" operands should be structurally same. So at some cases, hacking SQL is an only way to make this stuff working as Arel still not provides a good dynamical way to compile complex SQLs. – Magruder