# Dynamic queries

**URL:** <https://www.ruby-forum.com/t/dynamic-queries/54413>\
**Category:** Rails\
**Created:** [March 4, 2006, 12:59am UTC](https://www.ruby-forum.com/t/dynamic-queries/54413 "2006-03-04T00:59:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![idrifter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e495f1/32.png) [@idrifter](https://www.ruby-forum.com/u/idrifter)\
**Post date:** [March 4, 2006, 12:59am UTC](https://www.ruby-forum.com/t/dynamic-queries/54413/1 "2006-03-04T00:59:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![idrifter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e495f1/32.png) [@idrifter](https://www.ruby-forum.com/u/idrifter)\
**Post date:** [March 4, 2006, 1:26am UTC](https://www.ruby-forum.com/t/dynamic-queries/54413/2 "2006-03-04T01:26:33Z")

</div>

Hi,

On 3/4/06, Josh R. [removed\_email\_address@domain.invalid](mailto: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](http://www.RealityForge.org)

---

<div class="post-metadata">

**Author:** ![idrifter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e495f1/32.png) [@idrifter](https://www.ruby-forum.com/u/idrifter)\
**Post date:** [March 4, 2006, 5:30am UTC](https://www.ruby-forum.com/t/dynamic-queries/54413/3 "2006-03-04T05:30:35Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![idrifter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e495f1/32.png) [@idrifter](https://www.ruby-forum.com/u/idrifter)\
**Post date:** [March 4, 2006, 1:41am UTC](https://www.ruby-forum.com/t/dynamic-queries/54413/4 "2006-03-04T01:41:37Z")

</div>

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