# Dynamic SQL Queries

**URL:** <https://www.ruby-forum.com/t/dynamic-sql-queries/134229>\
**Category:** Rails\
**Created:** [April 9, 2008, 5:01am UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229 "2008-04-09T05:01:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![fabiokr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/cdc98d/32.png) [@fabiokr](https://www.ruby-forum.com/u/fabiokr)\
**Post date:** [April 9, 2008, 5:01am UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229/1 "2008-04-09T05:01:43Z")

</div>

Hi there,  
I’m starting to learn Rails, and I have a question about SQL usage.  
I have some fields on my view that will be used as a search form.  
So, I have the search parameters, but what’s the best way to  
use them on a sql query?

let’s say I have 3 search parameters, and the user uses 2 of them.

Object.find(:condition =\> [“text LIKE ? AND text2 = ? AND number 3 =  
?”,“bla”,“ble”,3])

I can’t do this, because if the user send me only 2 parameters the query  
wont work.

Does anyone know what’s the best way to do this?

Thanks!

---

<div class="post-metadata">

**Author:** ![fabiokr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/cdc98d/32.png) [@fabiokr](https://www.ruby-forum.com/u/fabiokr)\
**Post date:** [April 9, 2008, 5:25am UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229/2 "2008-04-09T05:25:01Z")

</div>

Hi Fabio,

For searching, you’re probably better off in the long term using  
something like acts\_as\_ferret, or solr.

Sticking with SQL LIKE clauses gets difficult quickly, as you’ve  
discovered.

You could also consider using MySQL full text indexing - but I’m not  
sure how to do that.

I’ve had a lot of success with acts\_as\_ferret, and it’s very easy to  
setup. Details are here: [http://projects.jkraemer.net/acts\_as\_ferret/](http://projects.jkraemer.net/acts_as_ferret/)

- Barney

On Apr 9, 1:01 pm, Fabio K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![fabiokr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/cdc98d/32.png) [@fabiokr](https://www.ruby-forum.com/u/fabiokr)\
**Post date:** [April 9, 2008, 6:46am UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229/3 "2008-04-09T06:46:20Z")

</div>

You can pretty easily adapt your controller to the number of parameters  
the user returns from their data entry… just construct your condition  
according to the data you received.

---

<div class="post-metadata">

**Author:** ![fabiokr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/cdc98d/32.png) [@fabiokr](https://www.ruby-forum.com/u/fabiokr)\
**Post date:** [April 9, 2008, 7:51am UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229/4 "2008-04-09T07:51:08Z")

</div>

conditions\_string\_ary = []  
conditions\_param\_values = []

text = params[:text]  
unless text.blank?  
conditions\_string\_ary \<\< ‘text LIKE ?’  
conditions\_param\_values \<\< text  
end

text\_2 = params[: text\_2]  
unless text2.blank?  
conditions\_string\_ary \<\< ‘text2 = ?’  
conditions\_param\_values \<\< text2  
end

number\_3 = params[: number\_3]  
unless number\_3.blank?  
conditions\_string\_ary \<\< ‘number\_3 = ?’  
conditions\_param\_values \<\< number\_3  
end

conditions\_string = conditions\_string\_ary.join(" AND ")

Model.find(:all, :conditions =\> ([conditions\_string] +  
conditions\_params\_values))

There you are.

Julian.

Learn Ruby on Rails! Check out the FREE VIDS (for a limited time)  
VIDEO #3 out NOW!  
[http://sensei.zenunit.com/](http://sensei.zenunit.com/)

---

<div class="post-metadata">

**Author:** ![fabiokr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/cdc98d/32.png) [@fabiokr](https://www.ruby-forum.com/u/fabiokr)\
**Post date:** [April 10, 2008, 11:15pm UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229/5 "2008-04-10T23:15:01Z")

</div>

Also, wrap your values in ‘%X%’ instead of just ‘X’ so that you get  
substring matches.

I second the idea of using ferret or solr. You get all sorts of  
benefits with those: better indexing, faster searching, multi-field,  
and/or selectors, and more. The only reason I’d roll my own is if my  
needs were really constrained. In which case, what Julian posted would  
work very well.

-Danimal

---

<div class="post-metadata">

**Author:** ![fabiokr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/cdc98d/32.png) [@fabiokr](https://www.ruby-forum.com/u/fabiokr)\
**Post date:** [April 11, 2008, 1:19am UTC](https://www.ruby-forum.com/t/dynamic-sql-queries/134229/6 "2008-04-11T01:19:53Z")

</div>

Thanks for all the help!  
I have tried ferret, but i had problems with relationed tables.  
And as I could see, ferret is for text search only, right?  
I could’t get to do numerical searches like x \> 10 or something like  
that.