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.
blog comments powered by Disqus
