Is there an easy (and secure) way to search your database when you don''t know the number of parameters ahead of time? I can see doing it by concatenating together an sql query, but hopefully rails provides a more elegant way to do this with ActiveRecord without making a ginormous switch statement? For example, lets say my search form has 4 different input boxes. I only want the query to search on fields that are actually provied by the user when they submit. Thanks in advance. -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060303/0a4dde7b/attachment.html
Hi, On 3/4/06, Josh Rickard <josh.rickard@gmail.com> wrote:> Is there an easy (and secure) way to search your database when you don''t > know the number of parameters ahead of time? I can see doing it by > concatenating together an sql query, but hopefully rails provides a more > elegant way to do this with ActiveRecord without making a ginormous switch > statement? > > For example, lets say my search form has 4 different input boxes. I only > want the query to search on fields that are actually provied by the user > when they submit. Thanks in advance.I don''t know of any easy way to do this. The simplest would be something like criteria = [] criteria << ''1 = 1'' criteria << ActiveRecord::Base.quote("p1 LIKE %#{params[p1]}%") if params[:p1] criteria << ActiveRecord::Base.quote("p2 = %#{params[p2]}%") if params[:p2] ... query = criteria.join('' AND '') For more complex queries I tend to build a separate class. i.e. I have a search form that has about 20 parameters that may cause a search to work with joins and all sorts of wierdness. I have attached an example for you tou have a look -- Cheers, Peter Donald Blog: http://www.RealityForge.org -------------- next part -------------- A non-text attachment was scrubbed... Name: search.rb Type: application/octet-stream Size: 9363 bytes Desc: not available Url : http://wrath.rubyonrails.org/pipermail/rails/attachments/20060304/ab676665/search.obj
ok, i''m a total newbie, so take this for what it''s worse
(hint: not
much!); but how about doing something like that:
@keys = Array.new
params.each_key do |key|
@keys << "#{key} = :#{key}"
end
@query = @keys.join(" and ")
find(:all [@query, params])
at least it would be safe. but that''s of course only if you can use
all params, otherwise you would have to specifically filter them out.
Now, everybody feel free to point out how silly this is. But, trying
to chime in on other people''s problems is kinda helping me in my own
learning process.... :)
sebastian
On Mar 3, 2006, at 5:56 PM, Josh Rickard wrote:
> Is there an easy (and secure) way to search your database when you
> don''t know the number of parameters ahead of time? I can see
doing
> it by concatenating together an sql query, but hopefully rails
> provides a more elegant way to do this with ActiveRecord without
> making a ginormous switch statement?
>
> For example, lets say my search form has 4 different input boxes.
> I only want the query to search on fields that are actually provied
> by the user when they submit. Thanks in advance.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
Josh Rickard wrote:> Is there an easy (and secure) way to search your database when you don''t > know the number of parameters ahead of time? I can see doing it by > concatenating together an sql query, but hopefully rails provides a more > elegant way to do this with ActiveRecord without making a ginormous > switch statement? > > For example, lets say my search form has 4 different input boxes. I > only want the query to search on fields that are actually provied by the > user when they submit. Thanks in advance.Peter and Sebastian have made a couple of good suggestions, but there''s actually a much better, and easier, way to accomplish what you want. You need to take a look at the ez_where plug-in, developed by Ezra Zygmuntowicz. You can read all about it on Ezra''s blog: <http://brainspl.at/articles/2006/01/30/i-have-been-busy> There''s a download link, there, as well. It''s an excellent, Ruby-style solution to the problem of building a set of find conditions on the fly. -Brian