Hi, My default list view is basically a huge tabular list of data, which actually is what I want. However, I would like to be able to filter this list by various criteria. Searching through the docs I came upon some "filter" methods, but it appears to that it''s talking about a different kind of filter, with the majority of the info pertaining to validating data before it is saved, etc. So what''s my best bet for extracting a subset of data from the whole shebang? I can see this getting kinda messy insofaras putting code which should be relegated to a helper or a partial in the list template. Any suggestions would be most appreciated!
It depends how dynamic you want it, but the simplest thing I can think
of would be to use select statements on your rows, something like:
@rows_to_show = all_rows.select { |row| row.matches_some_condition }
sam
On 5/23/05, Lester Bangs
<dumpingrounds-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Hi,
>
> My default list view is basically a huge tabular list of data, which
actually
> is what I want. However, I would like to be able to filter this list by
> various criteria.
>
> Searching through the docs I came upon some "filter" methods, but
it appears
> to that it''s talking about a different kind of filter, with the
majority of
> the info pertaining to validating data before it is saved, etc.
>
> So what''s my best bet for extracting a subset of data from the
whole shebang?
> I can see this getting kinda messy insofaras putting code which should be
> relegated to a helper or a partial in the list template.
>
> Any suggestions would be most appreciated!
>
>
>
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
sam
http://www.magpiebrain.com/
On Monday 23 May 2005 17:14, Lester Bangs wrote:> My default list view is basically a huge tabular list of data, which > actually is what I want. However, I would like to be able to filter > this list by various criteria.You just need to provide a condition to the find method you use to retrieve the objects. Michael -- Michael Schuerig Nothing is as brilliantly adaptive mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org as selective stupidity. http://www.schuerig.de/michael/ --A.O. Rorty, The Deceptive Self
Michael Schuerig <michael@...> writes:> > On Monday 23 May 2005 17:14, Lester Bangs wrote: > > > My default list view is basically a huge tabular list of data, which > > actually is what I want. However, I would like to be able to filter > > this list by various criteria. > > You just need to provide a condition to the find method you use to > retrieve the objects. > > Michael >I''m not sure I understand how you could implement it how you are describing it without adding a bunch controller methods with similar code. Example.... Table called widgets with fields model number, type, color, and size. (For sake of example, there are no relationships defined even though there should be.) the default list controller: Widget.find( :all ) controller to show all green widgets: Widget.find_all_by_color( "green" ) controller to show all large widgets: Widget.find_all_by_size( "large" ) controller to show all tiny yellow submarines: Widget.find_all_by_size_and_color_and_type( "tiny", "yellow", "submarine" ) This looks like it could get very large very fast. Obviously there is an easier way... Just point me in the right direction!
On Monday 23 May 2005 21:48, Lester Bangs wrote:> Michael Schuerig <michael@...> writes: > > On Monday 23 May 2005 17:14, Lester Bangs wrote: > > > My default list view is basically a huge tabular list of data, > > > which actually is what I want. However, I would like to be able > > > to filter this list by various criteria. > > > > You just need to provide a condition to the find method you use to > > retrieve the objects.> I''m not sure I understand how you could implement it how you are > describing it without adding a bunch controller methods with similar > code. Example.... > > Table called widgets with fields model number, type, color, and size. > (For sake of example, there are no relationships defined even though > there should be.) > > the default list controller: > Widget.find( :all ) > > controller to show all green widgets: > Widget.find_all_by_color( "green" )[snip] From your examples I''m not sure whether you know that you can provide conditions in an explicit parameter. Thus your color examples becomes Widget.find(:all, :conditions => [''color = ?'', target_color]) Of course you can create the condition programmatically. Given columns - Array of columns to query comp_ops - Array of comparison operators values - Array of comparison values You can build a condition roughly like this condition_items = [] columns.zip(values, comp_ops).each do |col, value, op| # maybe take value into account somehow condition_items << "#{col} #{op} ?" end conditions = [ condition_items.join('' and '') ] + values Does that help? Michael -- Michael Schuerig Thinking is trying to make up mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org for a gap in one''s education. http://www.schuerig.de/michael/ --Gilbert Ryle
I''m actually in the middle of implementing a small form that limits
search results for my list:
[snip]
conditionsArray.push("email =
''#{@params[:email]}''") if @params
[:email] != ''''
conditionsArray.push("lastname =
''#{@params[:name]}'' or
firstname = ''#{@params[:name]}''") if @params[:name] !=
''''
[snip]
conditions = conditionsArray.join(", ")
@subscriber_pages, @subscribers =
paginate :subscriber, :per_page => 50, :conditions => conditions,
:order_by => @params
[:sortby]
I''m sure a Ruby expert can do better coding than that, but
it''s
working well for me right now. I just wish there was a "contains"
keyword for the WHERE clause in MySQL.
But my question: once the data is filtered and paginated, how do keep
passing the params values to the next page, so that the filter stays
intact? I don''t know what to put on the "next page" link
statement.
Hope this helps,
Brett
On May 23, 2005, at 5:17 PM, Michael Schuerig wrote:
> On Monday 23 May 2005 21:48, Lester Bangs wrote:
>
>> Michael Schuerig <michael@...> writes:
>>
>>> On Monday 23 May 2005 17:14, Lester Bangs wrote:
>>>
>>>> My default list view is basically a huge tabular list of data,
>>>> which actually is what I want. However, I would like to be
able
>>>> to filter this list by various criteria.
>>>>
>>>
>>> You just need to provide a condition to the find method you use to
>>> retrieve the objects.
>>>
>
>
>> I''m not sure I understand how you could implement it how you
are
>> describing it without adding a bunch controller methods with similar
>> code. Example....
>>
>> Table called widgets with fields model number, type, color, and size.
>> (For sake of example, there are no relationships defined even though
>> there should be.)
>>
>> the default list controller:
>> Widget.find( :all )
>>
>> controller to show all green widgets:
>> Widget.find_all_by_color( "green" )
>>
> [snip]
>
> From your examples I''m not sure whether you know that you can
provide
> conditions in an explicit parameter. Thus your color examples becomes
>
> Widget.find(:all, :conditions => [''color = ?'',
target_color])
>
> Of course you can create the condition programmatically. Given
>
> columns - Array of columns to query
> comp_ops - Array of comparison operators
> values - Array of comparison values
>
> You can build a condition roughly like this
>
> condition_items = []
> columns.zip(values, comp_ops).each do |col, value, op|
> # maybe take value into account somehow
> condition_items << "#{col} #{op} ?"
> end
>
> conditions = [ condition_items.join('' and '') ] + values
>
> Does that help?
>
> Michael
>
> --
> Michael Schuerig Thinking is trying to
> make up
> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org for a
gap in one''s
> education.
> http://www.schuerig.de/michael/ --Gilbert
> Ryle
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
My mistake, the line should have been
conditions = conditionsArray.join(" and ")
nOn May 23, 2005, at 9:51 PM, Brett Walker wrote:
> I''m actually in the middle of implementing a small form that
limits
> search results for my list:
>
> [snip]
> conditionsArray.push("email =
''#{@params[:email]}''") if
> @params[:email] != ''''
> conditionsArray.push("lastname =
''#{@params[:name]}'' or
> firstname = ''#{@params[:name]}''") if @params[:name]
!= ''''
> [snip]
> conditions = conditionsArray.join(", ")
> @subscriber_pages, @subscribers =
> paginate :subscriber, :per_page => 50, :conditions => conditions,
> :order_by =>
> @params[:sortby]
>
> I''m sure a Ruby expert can do better coding than that, but
it''s
> working well for me right now. I just wish there was a
"contains"
> keyword for the WHERE clause in MySQL.
>
> But my question: once the data is filtered and paginated, how do
> keep passing the params values to the next page, so that the filter
> stays intact? I don''t know what to put on the "next
page" link
> statement.
>
> Hope this helps,
> Brett
>
> On May 23, 2005, at 5:17 PM, Michael Schuerig wrote:
>
>
>> On Monday 23 May 2005 21:48, Lester Bangs wrote:
>>
>>
>>> Michael Schuerig <michael@...> writes:
>>>
>>>
>>>> On Monday 23 May 2005 17:14, Lester Bangs wrote:
>>>>
>>>>
>>>>> My default list view is basically a huge tabular list of
data,
>>>>> which actually is what I want. However, I would like to be
able
>>>>> to filter this list by various criteria.
>>>>>
>>>>>
>>>>
>>>> You just need to provide a condition to the find method you use
to
>>>> retrieve the objects.
>>>>
>>>>
>>
>>
>>
>>> I''m not sure I understand how you could implement it how
you are
>>> describing it without adding a bunch controller methods with
similar
>>> code. Example....
>>>
>>> Table called widgets with fields model number, type, color, and
>>> size.
>>> (For sake of example, there are no relationships defined even
>>> though
>>> there should be.)
>>>
>>> the default list controller:
>>> Widget.find( :all )
>>>
>>> controller to show all green widgets:
>>> Widget.find_all_by_color( "green" )
>>>
>>>
>> [snip]
>>
>> From your examples I''m not sure whether you know that you can
provide
>> conditions in an explicit parameter. Thus your color examples becomes
>>
>> Widget.find(:all, :conditions => [''color = ?'',
target_color])
>>
>> Of course you can create the condition programmatically. Given
>>
>> columns - Array of columns to query
>> comp_ops - Array of comparison operators
>> values - Array of comparison values
>>
>> You can build a condition roughly like this
>>
>> condition_items = []
>> columns.zip(values, comp_ops).each do |col, value, op|
>> # maybe take value into account somehow
>> condition_items << "#{col} #{op} ?"
>> end
>>
>> conditions = [ condition_items.join('' and '') ] +
values
>>
>> Does that help?
>>
>> Michael
>>
>> --
>> Michael Schuerig Thinking is trying to
>> make up
>> mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org
for a gap in one''s
>> education.
>> http://www.schuerig.de/michael/ --Gilbert
>> Ryle
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
On 24/05/2005, at 12:21 PM, Brett Walker wrote:> My mistake, the line should have been > > conditions = conditionsArray.join(" and ") > > nOn May 23, 2005, at 9:51 PM, Brett Walker wrote: > >> I''m actually in the middle of implementing a small form that limits >> search results for my list: >> >> [snip] >> conditionsArray.push("email = ''#{@params[:email]}''") if >> @params[:email] != '''' >> conditionsArray.push("lastname = ''#{@params[:name]}'' or >> firstname = ''#{@params[:name]}''") if @params[:name] != '''' >> [snip] >> conditions = conditionsArray.join(", ") >> @subscriber_pages, @subscribers = paginate :subscriber, :per_page >> => 50, :conditions => conditions, >> :order_by => >> @params[:sortby] >>Actually you should be doing something like if @params[:email] != '''' conditionsArray.push("email = ?") conditionsVars.push @params[:email] end if @params[:name] etc.. then conditions = conditionsArray.join(" and ") + conditionsVars to protect yourself from SQL injections... bodhi
Ah, that''s how you do the variable substitution. Thanks! I''m just learning Ruby and Rails, so I don''t have the finer points of the language yet. Brett On May 23, 2005, at 10:28 PM, bodhi wrote:> On 24/05/2005, at 12:21 PM, Brett Walker wrote: > > >> My mistake, the line should have been >> >> conditions = conditionsArray.join(" and ") >> >> nOn May 23, 2005, at 9:51 PM, Brett Walker wrote: >> >> >>> I''m actually in the middle of implementing a small form that >>> limits search results for my list: >>> >>> [snip] >>> conditionsArray.push("email = ''#{@params[:email]}''") if >>> @params[:email] != '''' >>> conditionsArray.push("lastname = ''#{@params[:name]}'' or >>> firstname = ''#{@params[:name]}''") if @params[:name] != '''' >>> [snip] >>> conditions = conditionsArray.join(", ") >>> @subscriber_pages, @subscribers = >>> paginate :subscriber, :per_page => 50, :conditions => conditions, >>> :order_by => >>> @params[:sortby] >>> >>> > > Actually you should be doing something like > > if @params[:email] != '''' > conditionsArray.push("email = ?") > conditionsVars.push @params[:email] > end > if @params[:name] > etc.. > > then > > conditions = conditionsArray.join(" and ") + conditionsVars > > to protect yourself from SQL injections... > > > bodhi > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Michael Schuerig <michael@...> writes:> > On Monday 23 May 2005 21:48, Lester Bangs wrote: > > Michael Schuerig <michael <at> ...> writes: > > > On Monday 23 May 2005 17:14, Lester Bangs wrote: > > > > My default list view is basically a huge tabular list of data, > > > > which actually is what I want. However, I would like to be able > > > > to filter this list by various criteria. > > > > > > You just need to provide a condition to the find method you use to > > > retrieve the objects. > > > I''m not sure I understand how you could implement it how you are > > describing it without adding a bunch controller methods with similar > > code. Example.... > > > > Table called widgets with fields model number, type, color, and size. > > (For sake of example, there are no relationships defined even though > > there should be.) > > > > the default list controller: > > Widget.find( :all ) > > > > controller to show all green widgets: > > Widget.find_all_by_color( "green" ) > [snip] > > From your examples I''m not sure whether you know that you can provide > conditions in an explicit parameter. Thus your color examples becomes > > Widget.find(:all, :conditions => [''color = ?'', target_color]) > > Of course you can create the condition programmatically. Given > > columns - Array of columns to query > comp_ops - Array of comparison operators > values - Array of comparison values > > You can build a condition roughly like this > > condition_items = [] > columns.zip(values, comp_ops).each do |col, value, op| > # maybe take value into account somehow > condition_items << "#{col} #{op} ?" > end > > conditions = [ condition_items.join('' and '') ] + values > > Does that help? > > Michael >ok, I''ve got it working.... sort of. Having discovered the magic of the @param hash, I can filter the data by altering the URL of the list view, a la: http://foobar.com/widgets/list?filter=red i did this by adding to the list controller: <at> widgets = Widget.find( :all, :include => :color, :conditions => "color = \"#{<at> params["filter"]}\"" ) which works great, but ideally what I''d like is to have a drop-down at the top of the list with a list of colors (which exists in a separate table) to choose from. select a color, click "Filter" and the ?filter= part of the URL is added and the filter is applied. I''m having no luck, however, with the form code that actually generates the filter parameter: <%= start_form_tag :action => ''list_filter'' %> <select id="widget_color_id" name="widget[color_id]"> <%= options_from_collection_for_select(<at> colors, "id", "color" ) %> </select> <%= submit_tag "Filter" %> <%= end_form_tag %> This generates the list just fine, but I can''t seem to get the selected list item to appear anywhere in @params when the submit button is clicked. As a result, the ?filter= part of the URL is not generated and I end up with a blank filter page. I know I''m missing something extremely obvious here, but I can''t figure it out. Any help, as always, much appreciated!
On Tuesday 24 May 2005 15:13, Lester Bangs wrote:> ok, I''ve got it working.... sort of. Having discovered the magic of > the @param hash, I can filter the data by altering the URL of the > list view, a la: > > http://foobar.com/widgets/list?filter=red > > i did this by adding to the list controller: > > <at> widgets = Widget.find( :all, :include => :color, :conditions => > "color = \"#{<at> params["filter"]}\"" )By all means, don''t put a value from the params hash directly into something that goes to the DB. It opens the way to SQL injection. Instead use :conditions => ["color = ?", @params["filter"]] That way any parameters that get substituted into the template are sanitized.> which works great, but ideally what I''d like is to have a drop-down > at the top of the list with a list of colors (which exists in a > separate table) to choose from. select a color, click "Filter" and > the ?filter= part of the URL is added and the filter is applied.Rails by default submits forms using the "post" method (as opposed to "get") where you don''t see the request parameters. They''re there nonetheless.> I''m having no luck, however, with the form code that actually > generates the filter parameter: > > <%= start_form_tag :action => ''list_filter'' %> > <select id="widget_color_id" name="widget[color_id]"> > <%= options_from_collection_for_select(<at> colors, "id", "color" ) > %> </select> > <%= submit_tag "Filter" %> > <%= end_form_tag %>Have a look at the log/development.log. There search for "Parameters:". Michael -- Michael Schuerig Life is just as deadly mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org As it looks http://www.schuerig.de/michael/ --Richard Thompson, Sibella
Michael Schuerig <michael@...> writes:> [snip] > > > I''m having no luck, however, with the form code that actually > > generates the filter parameter: > > > > <%= start_form_tag :action => ''list_filter'' %> > > <select id="widget_color_id" name="widget[color_id]"> > > <%= options_from_collection_for_select(<at> colors, "id", "color" ) > > %> </select> > > <%= submit_tag "Filter" %> > > <%= end_form_tag %> > > Have a look at the log/development.log. There search for "Parameters:". >ooh, lots of good info in development.log. it seems to have ANSI escape sequences in it, for colorization I''m guessing... whats the best way to view it in all its colorized glory? windows cmd.exe doesn''t seem to understand it. thanks for the tips!
On May 24, 2005, at 9:26 AM, Michael Schuerig wrote:> On Tuesday 24 May 2005 15:13, Lester Bangs wrote: > > >> ok, I''ve got it working.... sort of. Having discovered the magic of >> the @param hash, I can filter the data by altering the URL of the >> list view, a la: >> >> http://foobar.com/widgets/list?filter=red >> >> i did this by adding to the list controller: >> >> <at> widgets = Widget.find( :all, :include => :color, :conditions => >> "color = \"#{<at> params["filter"]}\"" ) >> > > By all means, don''t put a value from the params hash directly into > something that goes to the DB. It opens the way to SQL injection. > Instead use > > :conditions => ["color = ?", @params["filter"]] > > That way any parameters that get substituted into the template are > sanitized. >Is there a reason why this doesn''t work for :order_by? :order_by => ["?", @params[:sortby]] fails as "ORDER BY ?firstname" Cheers, Brett
On Tuesday 24 May 2005 18:44, Brett Walker wrote:> On May 24, 2005, at 9:26 AM, Michael Schuerig wrote:> > By all means, don''t put a value from the params hash directly into > > something that goes to the DB. It opens the way to SQL injection. > > Instead use > > > > :conditions => ["color = ?", @params["filter"]] > > > > That way any parameters that get substituted into the template are > > sanitized. > > Is there a reason why this doesn''t work for :order_by? > > :order_by => ["?", @params[:sortby]]Yes: It''s not implemented that way. IMHO :order/:order_by (is one of them deprecated?) arguments should be sanitized. A common syntax with a single string could be maintained. File a bug report or patch. Michael -- Michael Schuerig This is not a false alarm mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org This is not a test http://www.schuerig.de/michael/ --Rush, Red Tide