Dynamic Find using LIKE

Rails
1

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

2

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

3

Steve R. 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 :slight_smile:

4

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

5

Stephen B. 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 :slight_smile:

6

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…

7

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.” [email protected]

8

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.” [email protected]

9

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.” [email protected]

Thanks for your help everyone. I appreciate it.