Forum: Ruby on Rails Specifying none/single/many conditions at the same time

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Szymon N. (Guest)
on 2006-03-13 22:16
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?
Szymon N. (Guest)
on 2006-03-14 16:44
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
Ezra Z. (Guest)
on 2006-03-14 21:54
(Received via mailing list)
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
Szymon N. (Guest)
on 2006-03-21 21:25
Thanks!

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

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]])?
Wilson B. (Guest)
on 2006-03-21 21:42
(Received via mailing list)
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.
Szymon N. (Guest)
on 2006-03-22 19:28
Thanks!

I'll do it similarly.

I just thought that with all this 'rails magic' it can be done in more
universal, 'magic' way :)
Ezra Z. (Guest)
on 2006-03-22 22:21
(Received via mailing list)
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 :)
>
> --

	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 ;)

http://brainspl.at/articles/2006/01/30/i-have-been-busy

Cheers

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid
This topic is locked and can not be replied to.