Hi all, I have a simple text box for the user to enter a search term. I think there is a helper that will convert a search term like "word_1 word_2" into a generated SQL query like this: SELECT * FROM table WHERE term LIKE ''%word_1%'' OR term LIKE ''%word_2%'' Does a helper method like this exist? Brandon -- 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 -~----------~----~----~----~------~----~------~--~---
Brandon S. wrote:> Hi all, > > I have a simple text box for the user to enter a search term. I think > there is a helper that will convert a search term like "word_1 word_2" > into a generated SQL query like this: > > SELECT * FROM table WHERE term LIKE ''%word_1%'' OR term LIKE ''%word_2%'' > > Does a helper method like this exist? > > BrandonSomething like this might do it: terms = [] params[:search_term].split('' '').each {|t| terms.push(''%'' + t + ''%'')} like_clause = terms.shift terms.each {|t| like_clause += ''OR term LIKE '' + t} Object.find(:all, :conditions => ''term LIKE'' + like_clause) steve -- 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 -~----------~----~----~----~------~----~------~--~---
Steve Rawlinson wrote:> Brandon S. wrote: > Does a helper method like this exist? >> >> Brandon > > Something like this might do it: > > terms = [] > params[:search_term].split('' '').each {|t| terms.push(''%'' + t + ''%'')} > like_clause = terms.shift > terms.each {|t| like_clause += ''OR term LIKE '' + t} > > Object.find(:all, :conditions => ''term LIKE'' + like_clause) > > steveWhat Steved write should go into the controller, and not helper as you post in your topic Brandon :) -- 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 -~----------~----~----~----~------~----~------~--~---
I personally feel that this should go in the model in a
''search''
method. You also need to santitize your terms. I use something like
this (untested):
class Book < ActiveRecord::Base
def self.search(terms)
terms = terms.split(" ")
conditions = ["", {}]
term_count = 0
terms.each do |term|
conditions[1][:"word_#{term_count}"] = "%#{term}%"
conditions[0] << "(my_column LIKE :word_#{term_count}) AND
"
term_count += 1
end
conditions[0].gsub!(/ AND$/)
find(:all, :conditions => conditions)
end
end
Your action can then look like this:
def search
@results = Book.search(params[:terms])
end
As mentioned above, this is untested code. It''s purely from memory to
demonstrate the concepts to consider and you''ll probably want to tidy
it up. The main points are: Use Rails''s built in condition handlers
so that you get santitized SQL and keep your controllers thin - you
never know when you''ll need to search your data outside of the web
controllers.
Hope that helps,
Steve
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
> Brandon S. wrote: >> Hi all, >> >> I have a simple text box for the user to enter a search term. I think >> there is a helper that will convert a search term like "word_1 word_2" >> into a generated SQL query like this: >> >> SELECT * FROM table WHERE term LIKE ''%word_1%'' OR term LIKE ''%word_2%'' >> >> Does a helper method like this exist? >> >> Brandon > > Something like this might do it: > > terms = [] > params[:search_term].split('' '').each {|t| terms.push(''%'' + t + ''%'')}Don''t forget to escape ''t'' above using whatever the db-escape method is called...> like_clause = terms.shift > terms.each {|t| like_clause += ''OR term LIKE '' + t} > > Object.find(:all, :conditions => ''term LIKE'' + like_clause)--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Stephen Bartholomew wrote:> I personally feel that this should go in the model in a ''search'' > method. You also need to santitize your terms. I use something like > this (untested): > > class Book < ActiveRecord::Base > def self.search(terms) > terms = terms.split(" ") > conditions = ["", {}] > term_count = 0ons => conditions)> end > end > > Your action can then look like this: > > def search > @results = Book.search(params[:terms]) > end >I agree with you Steve, models handle the database queries and the controller get the informations from the models :) -- 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 -~----------~----~----~----~------~----~------~--~---
To covert "word_1 word_2" to "SELECT * FROM table WHERE term LIKE ''%word_1%'' OR term LIKE ''%word_2%'' use Array''s join method.>> terms = "car boat train"=> "car boat train">> terms = terms.split(" ")=> ["car", "boat", "train"]>> sqlWHERE = "term LIKE ''%" << terms.join("%'' OR term LIKE ''%") + "%'' "=> "term LIKE ''%car%'' OR term LIKE ''%boat%'' OR term LIKE ''%train%'' " On Jun 1, 12:32 pm, "Brandon S." <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi all, > > I have a simple text box for the user to enter a search term. I think > there is a helper that will convert a search term like "word_1 word_2" > into a generated SQL query like this: > > SELECT * FROM table WHERE term LIKE ''%word_1%'' OR term LIKE ''%word_2%'' > > Does a helper method like this exist? > > Brandon > > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
keep in mind querying with LIKE is a great performance hit. what you really need is full text search, using either built in Mysql engine (works on MyISAM tables only, so forget about foreign keys etc) or postgresql tsearch2 or ferret (lucene ruby port) or whatever solution you find appropriate for your dbms. LIKE is not an option, tho. On Jun 1, 9:32 pm, "Brandon S." <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi all, > > I have a simple text box for the user to enter a search term. I think > there is a helper that will convert a search term like "word_1 word_2" > into a generated SQL query like this: > > SELECT * FROM table WHERE term LIKE ''%word_1%'' OR term LIKE ''%word_2%'' > > Does a helper method like this exist? > > Brandon > > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
liquidautumn wrote:> keep in mind querying with LIKE is a great performance hit. > what you really need is full text search, using either built in Mysql > engine (works on MyISAM tables only, so forget about foreign keys etc) > or postgresql tsearch2 or ferret (lucene ruby port) or whatever > solution you find appropriate for your dbms. LIKE is not an option, > tho. > > On Jun 1, 9:32 pm, "Brandon S." <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>Thanks for your help everyone. I appreciate it. -- 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 -~----------~----~----~----~------~----~------~--~---