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! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Kreusch <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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! > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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/
On 09/04/2008, at 1:01 PM, Fabio Kreusch wrote:
>
> 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!
> --
> Posted via http://www.ruby-forum.com/.
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---