Rails 4 LIKE query - ActiveRecord adds quotes
SqlRuby on-RailsRubyPostgresqlActiverecordSql Problem Overview
I am trying to do a like query like so
def self.search(search, page = 1 )
paginate :per_page => 5, :page => page,
:conditions => ["name LIKE '%?%' OR postal_code like '%?%'", search, search], order => 'name'
end
But when it is run something is adding quotes which causes the sql statement to come out like so
SELECT COUNT(*)
FROM "schools"
WHERE (name LIKE '%'havard'%' OR postal_code like '%'havard'%')):
So you can see my problem. I am using Rails 4 and Postgres 9 both of which I have never used so not sure if its and an activerecord thing or possibly a postgres thing.
How can I set this up so I have like '%my_search%'
in the end query?
Sql Solutions
Solution 1 - Sql
Your placeholder is replaced by a string and you're not handling it right.
Replace
"name LIKE '%?%' OR postal_code LIKE '%?%'", search, search
with
"name LIKE ? OR postal_code LIKE ?", "%#{search}%", "%#{search}%"
Solution 2 - Sql
Instead of using the conditions
syntax from Rails 2, use Rails 4's where
method instead:
def self.search(search, page = 1 )
wildcard_search = "%#{search}%"
where("name ILIKE :search OR postal_code LIKE :search", search: wildcard_search)
.page(page)
.per_page(5)
end
NOTE: the above uses parameter syntax instead of ? placeholder: these both should generate the same sql.
def self.search(search, page = 1 )
wildcard_search = "%#{search}%"
where("name ILIKE ? OR postal_code LIKE ?", wildcard_search, wildcard_search)
.page(page)
.per_page(5)
end
NOTE: using ILIKE
for the name - postgres case insensitive version of LIKE
Solution 3 - Sql
While string interpolation will work, as your question specifies rails 4, you could be using Arel for this and keeping your app database agnostic.
def self.search(query, page=1)
query = "%#{query}%"
name_match = arel_table[:name].matches(query)
postal_match = arel_table[:postal_code].matches(query)
where(name_match.or(postal_match)).page(page).per_page(5)
end
Solution 4 - Sql
ActiveRecord is clever enough to know that the parameter referred to by the ?
is a string, and so it encloses it in single quotes. You could as one post suggests use Ruby string interpolation to pad the string with the required %
symbols. However, this might expose you to SQL-injection (which is bad). I would suggest you use the SQL CONCAT()
function to prepare the string like so:
"name LIKE CONCAT('%',?,'%') OR postal_code LIKE CONCAT('%',?,'%')", search, search)
Solution 5 - Sql
If someone is using column names like "key"
or "value"
, then you still see the same error that your mysql query syntax is bad. This should fix:
.where("`key` LIKE ?", "%#{key}%")
Solution 6 - Sql
Try
def self.search(search, page = 1 )
paginate :per_page => 5, :page => page,
:conditions => ["name LIKE ? OR postal_code like ?", "%#{search}%","%#{search}%"], order => 'name'
end
See the docs on AREL conditions for more info.
Solution 7 - Sql
.find(:all, where: "value LIKE product_%", params: { limit: 20, page: 1 })