Rails 4 LIKE query - ActiveRecord adds quotes

Question

I am trying to do a like query like so  def self search search  page   1     paginate  per page   gt  5   page   gt  page       conditions   gt    name LIKE       OR postal code like         search  search     order   gt   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

User · Accepted Answer

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}%"

User · Answer

If someone is using column names like  quot key quot  or  quot value quot   then you still see the same error that your mysql query syntax is bad  This should fix   where  quot  key  LIKE   quot    quot    key   quot

User · Answer

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

User · Answer

find  all  where   value LIKE product     params    limit  20  page  1

User · Answer

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

User · Answer

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

User · Answer

Try   def self search search  page   1       paginate  per page   gt  5   page   gt  page         conditions   gt    name LIKE    OR postal code like         search        search        order   gt   name    end   See the docs on AREL conditions for more info

[sql] Rails 4 LIKE query - ActiveRecord adds quotes

Examples related to sql

Examples related to ruby-on-rails

Examples related to ruby

Examples related to postgresql

Examples related to activerecord