Dynamic SQL Queries

rubyonrails-talk
1

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!

2

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/

- Barney

3

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.

4

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/

5

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

6

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.