# Dynamic Find using LIKE

**URL:** <https://www.ruby-forum.com/t/dynamic-find-using-like/105725>\
**Category:** Rails\
**Created:** [June 1, 2007, 8:32pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725 "2007-06-01T20:32:27Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 1, 2007, 8:32pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/1 "2007-06-01T20:32:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 1, 2007, 9:29pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/2 "2007-06-01T21:29:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 1, 2007, 10:06pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/3 "2007-06-01T22:06:23Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 1, 2007, 11:41pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/4 "2007-06-01T23:41:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 2, 2007, 10:28am UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/5 "2007-06-02T10:28:58Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 2, 2007, 12:43am UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/6 "2007-06-02T00:43:27Z")

</div>

> > 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…

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 3, 2007, 5:41pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/7 "2007-06-03T17:41:21Z")

</div>

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.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 3, 2007, 8:30pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/8 "2007-06-03T20:30:44Z")

</div>

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.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![deniedlates](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/13edae/32.png) [@deniedlates](https://www.ruby-forum.com/u/deniedlates)\
**Post date:** [June 4, 2007, 6:56pm UTC](https://www.ruby-forum.com/t/dynamic-find-using-like/105725/9 "2007-06-04T18:56:39Z")

</div>

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.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

Thanks for your help everyone. I appreciate it.