Specifying none/single/many conditions at the same time


#1

Hi!

It’s rather ruby question than rails…
I have a single ‘list’ action where i call paginate method.
I can receive none, 1 or 2 variables that specify conditions for
paginate method.

How to write the code so i can call paginate without conditions if all
conditions variable are nil, with a single condition if one of
conditions variables is not empty and with 2 if both are not empty?

For 2 possible variables i could use few if statements, but if i could
have i.e. 4 or more?


#2

If anyone needed it, here it is (ruby is really simple!):

filters = []
filters << “column_name = ‘#{params[:column_name]}’” unless
params[:column_name].nil?

and so on…

filters = filters.join('and ')
@properties_pages, @properties = paginate :property, :order_by => ‘id’,
:conditions => filters, :per_page => 5


#3

On Mar 14, 2006, at 6:44 AM, szymek wrote:

Keep in mind that using it like this is leaving you open to sql
injection attacks. You need to use the [“column = ?”, params
[:column]] notation to keep your app safe from sql injection.

Cheers-
-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid


#4

Thanks!

Now i know how to delete a table using my own form :slight_smile:

I’m doing another, more complicated (but still simple) form to search
through the products i keep in my database. I’ve got lots of checkboxes
that specify type of the product, lower and upper bound of its price and
text field for specifying additional condition. I’ve put all of them
into ‘form’ hash.

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]])?


#5

On 3/21/06, szymek removed_email_address@domain.invalid 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.


#6

Thanks!

I’ll do it similarly.

I just thought that with all this ‘rails magic’ it can be done in more
universal, ‘magic’ way :slight_smile:


#7

On Mar 22, 2006, at 9:28 AM, szymek wrote:

Thanks!

I’ll do it similarly.

I just thought that with all this ‘rails magic’ it can be done in more
universal, ‘magic’ way :slight_smile:

This is the exact kind of dynamic query that I built the ez_where

plugin for. Have a look here for the magic i think your looking for :wink:

Cheers

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid