ActiveRecord condition building made easy

Posted by Andreas on Sunday, June 08, 2008 at 12:06 (CEST)

Building an ActiveRecord condition parameter can result in a lot of unattractive code if it needs to depend on various things. E.g. if you need to find records that match multiple criteria given by the user, you might end up writing a lot of code to do so.

Using a hash as a condition might work in some cases:

1 conditions = {}
2 conditions[:orderno] = orderno unless orderno.blank?
3 conditions[:color] = color unless color.blank?
4 ...
5 Product.find(:all, :conditions => conditions)

However this only works for simple searches (where values are matched by equality). If you try to build a slightly more complex condition (like searching for a substring in the name of a product, or for products that are cheaper than a given price), a hash won’t work and you need to use an array for the condition. This usually leads to clumsy code that needs to assemble the query string and its parameters in the right order depending on the given criteria. However, by extending the Array class with a helper method, everything becomes easier and nicer:

 1 
 2 
 3 class Array
 4   def add_condition! (condition, conjunction = 'AND')
 5     if String === condition
 6       add_condition!([condition])
 7     elsif Hash === condition
 8       add_condition!([condition.keys.map { |attr| "#{attr}=?" }.join(' AND ')] + condition.values)
 9     elsif Array === condition
10       self[0] = "(#{self[0]}) #{conjunction} (#{condition.shift})" unless empty?
11       (self << condition).flatten!
12     else
13       raise "don't know how to handle this condition type"
14     end
15     self
16   end
17 end

I originally found this idea in a DZone snippet and enhanced it a little bit.

Now, building the condition is nice, clean and DRY:

1 conditions = []
2 conditions.add_condition!(:orderno => orderno) unless orderno.blank?
3 conditions.add_condition!(:color => color) unless color.blank?
4 conditions.add_condition!(['name LIKE "%?%"', keyword]) unless keyword.blank?
5 conditions.add_condition!(['price >= ?', min_price]) unless min_price.blank?
6 conditions.add_condition!(['price <= ?', max_price]) unless max_price.blank?
7 Product.find(:all, :conditions => conditions)

Btw, if you were tempted to use a single string to build a condition, like conditions = "price >= #{max_price}", go and read about SQL injection. Never ever add user-input to your conditions without checking/escaping it first! Sadly, this seems to be a common mistake made by many people.

Tags , ,
8 comments • Permalink

Related posts

8 comments

Gravatar
• Aleks wrote about 1 hour later:

Nice idea. But how to make ranges work?

Somethig like:

User.find :all, :conditions => {:created_at => 5.days.ago..Time.now}

  1. SELECT * FROM `users` WHERE (`users`.`created_at` BETWEEN ‘2008-04-07 20:06:04’ AND ‘2008-04-12 20:06:04’)
Gravatar
• Andreas wrote about 14 hours later:

Indeed that’d be nice. Support for arrays as values is also missing yet

… :conditions => { :color => [‘red’, ‘blue’,‘green’] }

  1. … WHERE color IN (‘red’,‘blue’,‘green’)

It’d be nice to simply use one/some of the sanitize_sql* methods of ActiveRecord::Base to easily support all kind of conditions – but unfortunately, those methods are all private and cannot be called from outside (at least not without ugly workarounds and risking that it breaks if the internals of AR change).

Gravatar
• Melvin Ram wrote 2 days later:

This is excellent! I’m wondering, is there a way to do OR conditions. This is what I’m trying to do:

  1. Return all Enrollments where last_delivery_date is
  1. older than 1 day and delivery_time is in next 15 minutes
  1. or if the last_delivery_date is older than 2 days
Gravatar
• Andreas wrote 3 days later:

There is :). You can use the second parameter (conjunction, which defaults to ‘AND’) to use a different conjunction:

1 ['cond_a'].add_condition!(['cond_b'])       # => 'cond_a AND cond_b'
2 ['cond_a'].add_condition!(['cond_b'], 'OR') # => 'cond_a OR cond_b'
Gravatar
• Michael Fairchild wrote 3 months later:

sometimes I have a default condition, and just want to be able to add a condition if it is passed in.

I added 2 lines so that it would work with optional conditions, elsif condition.nil? self
Gravatar
• François wrote 10 months later:

I used named_scopes with a lambda that has a conditional in it. Then string the named scopes together. Is clean, effective, and allows you to reuse the code easily.

Gravatar
• Pratik Khadloya wrote about 1 year later:

Fixed a problem in the above when adding an Array of conditions to an Array of conditions.

 1 elsif Array === condition
 2       if self.empty?
 3         #Example:
 4         #self = []
 5         #condition = ["(modulable_type=?)", "2009-08-22"]
 6         self[0] = "(#{self[0]}) #{conjunction} (#{condition.shift})" unless empty?
 7          (self << condition).flatten!
 8       else
 9         #Example:
10         #self = ["(modulable_type=?) AND (parent_id=?) AND (status=?)","Task","2","20"]
11         #condition = ["(tasks.due_date=?)", "2009-08-22"]
12         self[0] = self[0] + " AND " + condition[0]
13         condition.length.times do |i|
14           if i != 0 #Skip the first element
15             self << condition[i]     
16           end
17         end
18       end

Example:

1 a = ["(modulable_type=?) AND (parent_id=?) AND (status=?)","Task","2","20"]
2 b = ["(tasks.due_date=?)", "2009-08-22"]

a.add_condition!(b) should give the following after the fix

c = [“(modulable_type=?) AND (parent_id=?) AND (status=?) AND (tasks.due_date=?)”,“Task”,“2”,“20”,“2009-08-22”]

Gravatar
• Pratik Khadloya wrote about 1 year later:

Added an array to string method when using with eval method:

ex:

1 eval("Modul.count(" + task_modul_join + " :conditions => " + conditions_array.to_s! + ")")
 1 def to_s!
 2     if Array === self
 3       conditions_string = "["
 4       self.length.times do |i|
 5         conditions_string.concat('"' + self[i].to_s + '",')
 6       end
 7       conditions_string = conditions_string.first(conditions_string.length - 1)
 8       conditions_string = conditions_string + "]"
 9     elsif
10       raise "Can only convert Array to_s!"
11     end
12   end

Comments are closed