Forum: Ruby on Rails dynamic queries

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.
Josh R. (Guest)
on 2006-03-04 01:59
(Received via mailing list)
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 D. (Guest)
on 2006-03-04 02:26
(Received via mailing list)
Hi,

On 3/4/06, Josh R. <removed_email_address@domain.invalid> 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 D.

Blog: http://www.RealityForge.org
Sebastian F. (Guest)
on 2006-03-04 02:41
(Received via mailing list)
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
Brian V. Hughes (Guest)
on 2006-03-04 06:30
(Received via mailing list)
Josh R. 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
This topic is locked and can not be replied to.