Newbie question - using sql "Like '%...." in Find problem

Rails
1

Hi all,

I’m trying to implement a simple search box:

@items = Item.find(:all, :conditions => [“description like %?% or text
like %?%”, @search_text, @search_text])

The problem with this is rails automatically encloses the search term in
single quotes, resulting in this MySQL error:

Mysql::Error: #42000You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘%‘stuff’% or text like %‘stuff’%)’ at line 1: SELECT

  • FROM items WHERE (description like %‘stuff’% or text like %‘stuff’%)

Is there a simple (not necessarily elegant) way to fix this?

Thanks a lot!!!

2

Alex wrote:

Hi all,

I’m trying to implement a simple search box:

@items = Item.find(:all, :conditions => [“description like %?% or text
like %?%”, @search_text, @search_text])
Try:
@items = Item.find(:all, :conditions => [“description like ? or text
like ?”, “%#{@search_text}%”, “%#{@search_text}%”])

Note that putting the % operator in the beginning essentially forces a
full table scan (unless you have some sort of full text indexing
perhaps) and could result in very slow searches.

3

@items = Item.find(:all, :conditions => [“description like %?% or text
like %?%”, @search_text, @search_text])

Try Item.find(:all, :conditions => [“description like ? or text like ?”,
“%#{@search_text}%”, “%#{@search_text}%”])

4

Try putting the wildcards in the parameters instead of the query:

@items = Item.find(:all, :conditions => [“description like ? or text
like ?”, “%#@search_text%”, “%#@search_text%”])

5

Thanks all, putting wildcards in the parameters worked like a charm!!!