Dynamic SQL Queries

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!

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]

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.

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/

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

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs