CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3postgresqlrails-activerecord
Joel Grannas
Joel Grannas

Reputation: 2016

Dynamic Method with ActiveRecord, passing in hash of conditions

I am struggling with the best way to meta program a dynamic method, where I'll be limiting results based on conditions... so for example:

class Timeslip < ActiveRecord::Base

    def self.by_car_trans(car, trans)
        joins(:car)
        .where("cars.trans IN (?) and cars.year IN (?) and cars.model ILIKE ?", trans, 1993..2002, car)
        .order('et1320')
    end

end

Let's say instead of passing in my arguments, i pass in an array of conditions with key being the fieldname, and value being the field value. so for example, I'd do something like this:

i'd pass in [["field", "value", "operator"],["field", "value", "operator"]]

def self.using_conditions(conditions)
    joins(:car)
    conditions.each do |key, value|
      where("cars.#{key} #{operator} ?", value)
    end
end

However, that doesn't work, and it's not very flexible... I was hoping to be able to detect if the value is an array, and use IN () rather than =, and maybe be able to use ILIKE for case insensitive conditions as well...

Any advice is appreciated. My main goal here is to have a "lists" model, where a user can build their conditions dynamically, and then save that list for future use. This list would filter the timeslips model based on the associated cars table... Maybe there is an easier way to go about this?

Upvotes: 0

Views: 1371

Answers (2)

m_x
m_x

Reputation: 12564

First of all, you might find an interest in the Squeel gem.

Other than that, use arel_table for IN or LIKE predicates : 

 joins( :car ).where( Car.arel_table[key].in      values )
 joins( :car ).where( Car.arel_table[key].matches value  )

you can detect the type of value to select an adequate predicate (not nice OO, but still):

 column    = Car.arel_table[key]
 predicate = value.respond_to?( :to_str ) ? :in : :matches # or any logic you want

 joins( :car ).where( column.send predicate, value )

you can chain as many as those as you want:

 conditions.each do |(key, value, predicate)|
   scope = scope.where( Car.arel_table[key].send predicate, value )
 end
 return scope

Upvotes: 2

Alistair A. Israel
Alistair A. Israel

Reputation: 6567

So, you want dynamic queries that end-users can specify at run-time (and can be stored & retrieved for later use)?

I think you're on the right track. The only detail is how you model and store your criteria. I don't see why the following won't work:

def self.using_conditions(conditions)
  joins(:car)
  crit = conditions.each_with_object({}) {|(field, op, value), m|
    m["#{field} #{op} ?"] = value
  }
  where crit.keys.join(' AND '), *crit.values
end

CAVEAT The above code as is is insecure and prone to SQL injection.

Also, there's no easy way to specify AND vs OR conditions. Finally, the simple "#{field} #{op} ?", value for the most part only works for numeric fields and binary operators.

But this illustrates that the approach can work, just with a lot of room for improvement.

Upvotes: 0

Related Questions