Last Updated: January 20, 2017
·
33.33K
· ravicious

OR queries with arrays as arguments in Rails 4

tl;dr

query = Order.unscoped.where(uuid: uuids, id: ids)
Order.where(query.where_values.inject(:or))
#=> Order Load (0.7ms)  SELECT "orders".* FROM "orders" WHERE ("orders"."uuid" IN ('5459eed8350e1b472bfee48375034103', '21313213jkads', '43ujrefdk2384us') OR "orders"."id" IN (2, 3, 4))

Since the or method is not available in Rails 4, it's tricky to perform OR queries with arrays as arguments. Of course we can write raw SQL, but is there a simpler way to do it?

My coworker sent me a link to a discussion on ruby-forum.com. That's where we found the following code:

recipe_query = Recipe.where("pastry_id = 1").where("filling_id = 1")
Recipe.where(recipe_query.where_values.join(" OR "))

However, it didn't work. where_values returns an array of Arel::Nodes::In objects and we can't just join them.

There's a post on Stack Overflow where the author scoops into Arel methods.

Category.top_level.where_values.map(&:to_sql)
=> ["`categories`.`parent_id` IS NULL"]

Oh yeah, how could I forgot about to_sql? Strings in an array like this can be easily joined, so in our case the code looks as follows:

query = Order.where(uuid: uuids, id: ids)
Order.where(query.where_values.map(&:to_sql).join(" OR "))
#=> Order Load (0.7ms)  SELECT "orders".* FROM "orders" WHERE ("orders"."uuid" IN ('5459eed8350e1b472bfee48375034103', '21313213jkads', '43ujrefdk2384us') OR "orders"."id" IN (2, 3, 4))

That's it! Where values are now joined by or, not and.

But wait! We can do it better. Cameron Martin on StackOverflow posted a little more elegant version:

query = Order.where(uuid: uuids, id: ids)
Order.where(query.where_values.inject(:or))

Rails 4.1+

Rails 4.1 treats default_scope just as a regular scope. The default scope (if you have any) is included in the where_values result and inject(:or) will add or statement between the default scope and your wheres. That's bad.

To solve that, you just need to unscope the query.

query = Order.unscoped.where(uuid: uuids, id: ids)
Order.where(query.where_values.inject(:or))

Possible caveats

When performing a query, elements of the array are cast to corresponding types, so be careful.

ids = ["5459eed8350e1b472bfee48375034103", "2", "3"]
query = Order.where(uuid: ids, id: ids)
Order.where(query.where_values.map(&:to_sql).join(" OR "))
#=> Order Load (0.6ms)  SELECT "orders".* FROM "orders" WHERE ("orders"."uuid" IN ('5459eed8350e1b472bfee48375034103', '2', '3') OR "orders"."id" IN (5459, 2, 3))

9 Responses
Add your response

You can also use arel:

Order.where(Order.arel_table[:uuid].in(ids).or(Order.arel_table[:id].in(ids)))

With arel-helpers we can make it a little shorter:

Order.where(Order[:uuid].in(ids).or(Order[:id].in(ids)))

And it's much more flexible since you can combine any or/and groups

over 1 year ago ·

Hi jandudulski, thanks for the arel addition - but only the first one works - the second generates an error:

NoMethodError: undefined method `[]' for #<Class:0x00000009741518>
over 1 year ago ·

@limeblast I think you have to install the mentioned arel-helpers gem to make it work.

over 1 year ago ·

Ah, ok. Sorry, I'm fairly new to ruby/rails, and wasn't aware that arel-helpers was something different. Thank you for clearing that up for me.

over 1 year ago ·

No problem. If you're new to Rails and you won't write queries like that too often, I'd recommend the first method or the one I described in the post, so you won't trouble yourself with libraries that do a bit of magic you don't yet grok ;)

over 1 year ago ·

Hi. Im getting an error when trying this with Rails 4.2.0. The error is: PG::ProtocolViolation: ERROR: bind message supplies 0 parameters, but prepared statement "" requires 2. Any ideas how I can get round this?

Edit: Apparently a known bug. I got round this like so:

scope :first_payment_between, -> (from, to) {
    joins(self.first_card_payment_between(from,to).values[:joins])
    .joins(self.first_direct_payment_between(from,to).values[:joins])
    .where(first_card_payment_between(from,to).ast.cores.last.wheres.inject{|ws, w| (ws &&= ws.and(w)) || w}
    .or(first_direct_payment_between(from,to).ast.cores.last.wheres.last))
  }

Hope that helps someone :-)

over 1 year ago ·

Isn't the query var an Active Record Relation? Not really an array, right? or did I miss something?

over 1 year ago ·

@random-private: You're right, it's an ActiveRecord relation.

over 1 year ago ·

There is a gem that support OR queries in Rails 3 and 4 now.
rails_or

over 1 year ago ·