Rails find :conditions

The Class ActiveRecord::Base provides an interesting method to execute searches on the database without the need to write SQL queries. It is the find method.

The find method has some retrieval options (:all, :first, :last, :id) and many parameters which are detailed in the links above. In this article we'd like to focus on the :conditions parameter.

Conditions can either be specified as a string, array, or hash representing the WHERE-part of an SQL statement.

String
This is not a safe way to use the find method with the conditions parameter, because the values passed are not sanitized and inserted directed into the resulting SQL query assembled by the ActiveRecord class. This method is more susceptible to SQL-injection attacks.

Archive.find(:all, :conditions => "size > '#{size}' AND updated_at > '#{2.days.ago}'"


Array
This is safer than the String condition described above, but still demands one to write a long string with lots of "?" question marks. One can easily get lost counting and matching those.

Archive.find(:all, :conditions => ["size > ? AND updated_at > ?",size,2.days.ago])


Hash
This is as safe as the Array condition described above with the advantage of being simpler to use.

Archive.find(:all, :conditions => ["size > :size AND updated_at > :time_par",{:size => size, :time_par => 2.days.ago}])


Find below some examples on more flexible ways to use the:conditions parameter.

Task.find(:all, :conditions => ["complete=? and priority=?", false, 3])
Task.find(:all, :conditions => ["complete=? and priority IS ?", false, nil])
Task.find(:all, :conditions => ["complete=? and priority IN (?)", false, [1,3]])
Task.find(:all, :conditions => ["complete=? and priority IN (?)", false, 1..3])

Task.find(:all, :conditions => { :complete => false, :priority => 1 })
Task.find(:all, :conditions => { :complete => false, :priority => nil })
Task.find(:all, :conditions => { :complete => false, :priority => [1,3] })
Task.find(:all, :conditions => { :complete => false, :priority => 1..3 })

Task.find_all_by_priority(1..3)

Some links on this matter that might be interesting (though quite old) are this screencast and this "generic" conditions block from this article.

Have fun!