[Sql] Rails 4 LIKE query - ActiveRecord adds quotes


Answers

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

Question

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?




.find(:all, where: "value LIKE product_%", params: { limit: 20, page: 1 })



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.




Links