Rails 4 LIKE query - ActiveRecord adds quotes
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)
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
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}%"
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