Dynamic queries

Rails
1

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.

2

Hi,

On 3/4/06, Josh R. [email protected] 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

3

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

4

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… :slight_smile:

sebastian