Self.where('section = ?', '%:search_item%')

Rails
1

I am trying to do a search where I can enter a part of a word and the
search find all occurrences of that part word in the field. The above
subject does not work. Can anyone help
I am using Rails 3 and ruby 1.9.2
I use to be able to do it in rails 2.x.
In my Model I have the following:-
def self.search(search_item)
if search_item
self.where(‘section = ?’, ‘%:search_item%’)
else
self.all
end
end

In my controller I have:-
@homepages = Homepage.search(params[:search])
respond_to do |format|
format.html # index.html.erb
format.xml { render :xml => @homepages }
format.js {render :js => @homepages}
end

2

self.where(‘section = %?%’, search_item)

3

Thanks for that, but I am still getting a syntax error. Now:-
SQLite3::SQLException: near “%”: syntax error: SELECT
“homepages”.* FROM “homepages” WHERE (section = %‘Gar’%)

This shows that the correct text is being passed, but the syntax is
still incorrect.
Don

4

Thanks for that, but I am still getting a syntax error. Now:-
SQLite3::SQLException: near “%”: syntax error: SELECT
“homepages”.* FROM “homepages” WHERE (section = %‘Gar’%)

This shows that the correct text is being passed, but the syntax is
still incorrect.
Don

5

On 31 October 2010 12:09, MDM [email protected] wrote:

Thanks for that, but I am still getting a syntax error. Now:-
SQLite3::SQLException: near “%”: syntax error: SELECT
“homepages”.* FROM “homepages” WHERE (section = %‘Gar’%)

You can’t put the question mark inside the percentage signs, as Rails
SQL-escapes the variable and wraps it in appropriate delimiters…
hence your error. You need to wrap your string in percentages one way
or another and pass that in:

self.where(‘section = ?’, “%#{search_item}%”)

6

Thanks Michael
I have tried that along with other versions of ‘%search_item%‘without
any joy. Just tried it again. it does not throws an error, but it
returns nothing.
self.where(‘section = ?’, search_item) works with the full word
‘Gardening’, but not ‘Gar’
self.where(‘section = ?’, ‘%search_item%’) does not work and you
cannot use %without a ’ or "
self.where(‘section = ?’, ‘%‘search_item’%’) is an internal
server error
self.where(‘section = ?’, ‘%’+search_item+’%’) no go either.
Don

7

Wow Thanks Michael
After all this time.
Both of these works which do you think is best.
self.where(‘section LIKE ?’, “%#{search_item}%”)
self.where(‘section LIKE ?’, ‘%’+search_item+’%’)
I can now get back to the remote => ajax call.
Pure joy Thanks again Don

8

On 31 October 2010 19:36, MDM [email protected] wrote:

Thanks Michael
I have tried that along with other versions of '%search_item%'without
self.where(‘section = ?’, ‘%search_item%’) does not work and you
cannot use %without a ’ or "

Oops… just spotted…
if you’re trying to do a SQL query with “%” you need to use “LIKE”
instead of “=”

self.where(‘section LIKE ?’, “%#{search_item}%”)

9

On 31 October 2010 20:05, MDM [email protected] wrote:

Both of these works which do you think is best.
self.where(‘section LIKE ?’, “%#{search_item}%”)
self.where(‘section LIKE ?’, ‘%’+search_item+‘%’)

personally, I prefer the syntax of the first method; it seems more
“Ruby”.

10

Michael P. wrote in post #958364:

On 31 October 2010 20:05, MDM [email protected] wrote:

Both of these works which do you think is best.
self.where(‘section LIKE ?’, “%#{search_item}%”)
self.where(‘section LIKE ?’, ‘%’+search_item+‘%’)

personally, I prefer the syntax of the first method; it seems more
“Ruby”.

It also performs slightly better, since it doesn’t create extra String
objects.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]