Where developers come to connect, share, build and be inspired.

8

OR queries with arrays as arguments in Rails 4

1102 views

tl;dr

query = Order.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))

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

Comments

Add a comment