Dynamic SQL Queries

Rails
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

On Apr 9, 1:01 pm, Fabio K. [email protected]

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.