RailsCasts Pro episodes are now free!

Learn more or hide this

Fun with Find Conditions

#15 Fun with Find Conditions

Apr 06, 2007 | 4 minutes | Active Record
You can pass more than simple strings to find conditions. Arrays, ranges, and nil values can be passed as well. In this episode you will see the tricks involved with passing these odd objects to find conditions. (Update: audio fixed).
Click to Play Video ▶
  • Download:
  • mp4Full Size H.264 Video (11.1 MB)
  • m4vSmaller H.264 Video (6.77 MB)
  • webmFull Size VP8 Video (15.8 MB)
  • ogvFull Size Theora Video (17.9 MB)
Es It Fr Zh

This episode discusses find conditions. Below are some lines from script/console, shown with the SQL that they generate.

Task.find(:all, :conditions => ["completed = ? AND priority = ?", false, 2]) SELECT * FROM "tasks" WHERE (completed = 'f' AND priority = 2)

Finding all uncompleted tasks with a priority of 2.

The example above works, but what if we’re looking for the Tasks that have a priority that is nil? Task.find(:all, :conditions => ["completed = ? AND priority = ?", false, nil]) SELECT * FROM "tasks" WHERE (completed = 'f' AND priority = NULL)

Searching for null values generates incorrect SQL.

The SQL generated above is incorrect. When searching for null values the correct syntax would be priority IS NULL, not priority = NULL. Similarly if we were searching for more than one priority value using an array or range we’d have to change the find string to use IN rather than =, and put brackets around the second question mark in order to ensure that the SQL generated had the correct syntax.

Task.find(:all, :conditions => ["completed = ? AND priority IN (?)", false, [1,3]]) SELECT * FROM "tasks" WHERE (completed = 'f' AND priority IN (1,3))

Since Rails 1.2 there has been a better way to pass conditions: via a hash. Using a hash of conditions ensures that Rails uses the correct conditions when querying the database.

Task.find(:all, :conditions => { :completed => false, priority => [1,3] } SELECT * FROM "tasks" WHERE (completed = 'f' AND priority IN (1,3)

Using a hash of conditions with an array parameter.

Task.find(:all, :conditions => {:completed => false, priority => 2..4})SELECT * FROM "tasks" WHERE ("tasks"."priority" BETWEEN 2 AND 4 AND "tasks"."completed" = 'f')

Pass a range and BETWEEN will be used in the SQL.

Using with dynamic finds

Back in episode 2 we used dynamic find_by methods. These can also take nil, array or range arguments.

Task.find_by_priority(1..5)SELECT * FROM "tasks" WHERE ("tasks"."priority" BETWEEN 1 AND 5) LIMIT 1

Using a hash of find conditions ensures that Rails generates the correct SQL when passing any type of parameter to the find.