Forum: Ruby on Rails Dynamic Find using LIKE

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
9e7cdfa62c6bc2a155f2f7950484e573?d=identicon&s=25 Brandon S. (deniedlates)
on 2007-06-01 20:32
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
19990e3fa0fc68c874f42f13d1500011?d=identicon&s=25 Steve Rawlinson (trip)
on 2007-06-01 21:29
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 + '%')}
like_clause = terms.shift
terms.each {|t| like_clause += 'OR term LIKE ' + t}

Object.find(:all, :conditions => 'term LIKE' + like_clause)

steve
E3513c4edd6810bb4b9914b58da2a2c3?d=identicon&s=25 Jamal Soueidan (jamal)
on 2007-06-01 22:06
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)
>
> steve

What Steved write should go into the controller, and not helper as you
post in your topic Brandon :)
6d57db44c5a4e0721cc7f78cf0ce308a?d=identicon&s=25 Stephen Bartholomew (steveb)
on 2007-06-01 23:41
(Received via mailing list)
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
2f9a03aa0fcfe945229cb6126eda2cb2?d=identicon&s=25 Philip Hallstrom (Guest)
on 2007-06-02 00:43
(Received via mailing list)
>>
>> 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...
E3513c4edd6810bb4b9914b58da2a2c3?d=identicon&s=25 Jamal Soueidan (jamal)
on 2007-06-02 10:28
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 = 0
ons => 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 :)
86510176fca94533818841c9be3db9e9?d=identicon&s=25 Scott Meade (Guest)
on 2007-06-03 17:41
(Received via mailing list)
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...@andreas-s.net>
8608f2d7f02c6d0f1c349b7b94b1cea5?d=identicon&s=25 liquidautumn (Guest)
on 2007-06-03 20:30
(Received via mailing list)
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...@andreas-s.net>
9e7cdfa62c6bc2a155f2f7950484e573?d=identicon&s=25 Brandon S. (deniedlates)
on 2007-06-04 18:56
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...@andreas-s.net>


Thanks for your help everyone.  I appreciate it.
This topic is locked and can not be replied to.