Back in October 2005, I wrote an article entitled Building the SQL WHERE Clause Dynamically. This article is obsolete and I’m replacing it with this one instead.
What do you do with a FORM that has multiple conditions? Something like this:
The way I would do it today is using named scopes:
app/models/person.rb
1 class Person < ActiveRecord::Base
2 is_taggable
3
4 named_scope :in_region,
5 lambda {|region| {:conditions => {:region => region}}}
6 named_scope :with_salary,
7 lambda {|lower, upper| {:conditions =>
8 {:salary => (lower .. upper)}}}
9
10 # Elided, but something similar to the above
11 named_scope :tagged_with,
12 lambda {|tags| {:conditions =>
13 …}}
14 end
Then you need your controller to translate between what the view provides and what the model expects:
app/controllers/searches_controller.rb
1 class SearchesController < ApplicationController
2 def show
3 root = Person
4 root = root.in_region(params[:region]) unless params[:region] =~ /all/i
5 unless params[:salary] =~ /all/i
6 params[:salary].gsub!("K", "000")
7 lower, upper = params[:salary].split("-")
8 root = root.with_salary(lower, upper)
9 end
10 root = root.tagged_with(Tag.parse(params[:tags])) unless params[:tags].blank?
11 @people = root.paginate(:page => params[:page])
12 end
13 end
For simpler forms, the approach above would be sufficient. For more complex forms, I suggest you look at plugins that aim to provide more structure around your SQL clauses, such as:
I also suggest looking at Searching and Queries Plugins – Agile Web Development to find other plugins.
