On 3/21/06, szymek [email protected] wrote:
Is there any way to dynamically create conditions for sql where
statement using notation used in the previous post based on received
hash ([“column = ?”, params[:column]])?
I can’t tell which message this is a reply to, but I recognize your
new question. Heh.
I’ve had to solve this problem before, where the only way to get the
correct result set was to remove certain parameters from the
:conditions array. However, I didn’t want to just build up the string
by hand, because then I would need to duplicate the SQL-quoting
features that ActiveRecord provides.
So, I added a ‘search_by’ method to the model class. In this case,
“l2” and “be” are the associated models that I needed to filter on.
What they represent in the real world isn’t important to the example,
IMO.
class Plan < ActiveRecord::Base
def self.search_by(l2, be, year, month)
if l2.nil? && be.nil? # No L2 or BE specified.
conditions = “”;parameters = []
elsif be.nil? # L2 specified.
conditions = "l2_codes.code = ? and "
parameters = [l2]
elsif l2.nil? # BE specified.
conditions = "budget_entities.code = ? and "
parameters = [be]
else # Both L2 and BE specified.
conditions = "budget_entities.code = ? and l2_codes.code = ? and "
parameters = [be, l2]
end
# Construct the actual query from the conditions and parameters.
# All ‘list_plan’ queries specify a year and month.
query = [conditions + ‘plan_year = ? and plan_month = ?’]
query += parameters + [year, month]
end
end
Then, in the controller action that actually needed this functionality:
def list_plans
@page_title = “Plan List”
l2, be = prepare_search_params(params[:l2], params[:be])
year = params[:year];month = params[:month]
Produce a text description for this set of results.
@description = prepare_description(l2, be)
Paginate the results, using conditions prepared by the model.
@plan_pages, @plans = paginate :plans,
:conditions => Plan.search_by(l2,be,year,month),
:include => [:budget_entity, :l2_code]
if @plans.empty?
flash[:notice] = “No Plans found with those criteria.”
redirect_to :action => ‘search’
end
end
This was also an attempt to fit this kind of fiddly conditional stuff
into a “Composed Method” pattern, a term coined by Kent Beck for a
method where every step operates at the same level of detail. I think
it works OK, and it’s definitely nicer than putting all the if/else
logic into the controller.
Hopefully that helps, and doesn’t confuse the issue.