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

10

OR queries with arrays as arguments in Rails 4

5701 views


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

Comments

  • Fe0ce83e4e1683c3aeda7cb2160e06a1

    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

  • Mangayourface

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

    NoMethodError: undefined method `[]' for #<Class:0x00000009741518>
    
  • Rafal_cieslak-500kb-square

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

  • Mangayourface

    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.

  • Rafal_cieslak-500kb-square

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

  • None

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

  • None

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

  • Rafal_cieslak-500kb-square

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

Add a comment