OR operator in WHERE clause with Arel in Rails 4.2

Question

The following code constructed a valid where clause with an OR operator in Rails 4.1

MyModel.where(
  MyModel.where(attribute1: 1, attribute2: 2).where_values.reduce(:or)
)

Which is roughly equivalent to the SQL

select * from my_models where (attribute1 = 1 OR attribute2 = 2)

In Rails 4.2, the same code generates an SQL query with missing values for it's bind parameters

select * from my_models where attribute1 =  OR attribute2 =

... and generates an error due to the missing values for the bound values.

What is the equivalent code in Rails 4.2 to generate a valid query with an OR operator?

Edit:

The solution requires an Arel::Nodes::Node derived object to be used so that it can itself be combined with other conditions via AND and OR groupings.

rel = MyModel.where(attribute1: 1, attribute2: 2)
conditions = [rel.where_values.reduce(:or).to_sql, *rel.bind_values.map(&:last)]

MyModel.where(conditions)

The conditions var must be a derivative of Arel::Nodes::Node. The above solution works for simple queries, but for more complicated queries, conditions must be an Arel Node to be passed to a final query method.

where_values was removed from Rails 4.2 ... at least when I search the APIs I can find it in 3.x but not in 4.2. That may well be the source of your issue. — craig.kaminsky, Dec 23 '14 at 20:23
where_values is part of the private API in Rails 4.2... it still exists. The bind values are in bind_values... also private. The recent changes to Arel via AdequateRecord are the cause for the issue. — akaspick, Dec 23 '14 at 20:28
Ok, good deal. Was not 100% certain since it was 'removed' from the public API (hence the comment v. answer). — craig.kaminsky, Dec 23 '14 at 20:33
What does `Model.where(conditions).where_values.reduce(:or).to_sql` return? — SHS, Dec 23 '14 at 20:40
[May be of help](http://stackoverflow.com/q/31096009/3444240) — potashin, Jul 21 '15 at 02:32

score 5 · Answer 1 · answered Apr 28 '15 at 10:56

I'm using the below until rails 5 is out (in rails 5 AR supports .or):

ActiveRecord::QueryMethods::WhereChain.class_eval do
  def or(*scopes)
    scopes_where_values = []
    scopes_bind_values  = []
    scopes.each do |scope|
      case scope
      when ActiveRecord::Relation
        scopes_where_values += scope.where_values
        scopes_bind_values += scope.bind_values
      when Hash
        temp_scope = @scope.model.where(scope)
        scopes_where_values += temp_scope.where_values
        scopes_bind_values  += temp_scope.bind_values
      end
    end
    scopes_where_values = scopes_where_values.inject(:or)
    @scope.where_values += [scopes_where_values]
    @scope.bind_values  += scopes_bind_values
    @scope
  end
end

With the above you can do:

MyModel.where.or(attribute1: 1, attribute2: 2)
# or
MyModel.where.or(MyModel.where(some conditions), MyModel.where(some other conditions))

For anyone reading this and still at rails 4+. This goes as an initializer, it's a monkey patch — tommyalvarez, Nov 22 '18 at 00:23

score 3 · Answer 2 · answered May 04 '15 at 16:11

3

Using raw arel might be a better option:

t = MyModel.arel_table
MyModel.where(
  t[:attribute1].eq(1).or(
    t[:attribute2].eq(2)
  )
)

answered May 04 '15 at 16:11

Mike Auclair

373
2
8

eagleas · Answer 3 · 2016-01-19T12:11:50.310

More correctly solution based on @bsd answer, but allow arbitrary scopes on input

 ActiveRecord::QueryMethods::WhereChain.class_eval do
   def or(*scopes)
     scopes_where_values = []
     scopes_bind_values  = []
     scopes.each do |scope|
       case scope
       when ActiveRecord::Relation
         scopes_where_values << scope.where_values.reduce(:and)
         scopes_bind_values += scope.bind_values
       when Hash
         temp_scope = @scope.model.where(scope)
         scopes_where_values << temp_scope.where_values.reduce(:and)
         scopes_bind_values  += temp_scope.bind_values
       end
     end
     scopes_where_values = scopes_where_values.inject(:or)
     @scope.where_values += [scopes_where_values]
     @scope.bind_values  += scopes_bind_values
     @scope
   end
 end

P.S. Previous code by @bsd can't correctly work in little difficult case: User.where.or(User.where(rating: 3), User.where(startups: { progress: 100, rating: nil })

Result of old code is wrong:

SELECT "users".* FROM "users" WHERE (("startups"."rating" = 3 OR "startups"."progress" = 100) OR "startups"."rating" IS NULL)

Changed code generate correct:

SELECT "users".* FROM "users" WHERE ("startups"."rating" = 3 OR "startups"."progress" = 100 AND "startups"."rating" IS NULL)

OR operator in WHERE clause with Arel in Rails 4.2

3 Answers3

Linked