MySQL Rails ILIKE Query

Question

My syntax is off. I want to create a scope by_namethat will find everything agencieswhose name attribute contains the passed string (case insensitive).

Here is what I have:

class Agency < ActiveRecord::Base
  scope :by_name, ->(agency_name) { where('name ILIKE ?', "%#{agency_name}%") }
end

In the rails console, type agencies = Agency.by_name("foo"). Here is the generated query:

SELECT `agencies`.* FROM `agencies`  WHERE (name ILIKE '%foo%')

Here is the error message:

Mysql2 :: Error: you have an error in the SQL syntax; check the manual that matches the version of MySQL server for the correct syntax to use next to 'ILIKE'% foo% ')

Answer 1

I think it should be:

 scope :by_name, lambda { |agency_name| 
   where('name LIKE ?', "%#{agency_name}%") # not ILIKE
 }

PostgreSQL ILIKE LIKE, . SQL, PostgreSQL.

MySQL ILIKE. MySQL .

---

- Arel. :

scope :by_name, lambda { |agency_name| 
  where(Agency.arel_table[:name].matches("%#{agency_name}%"))
}

Answer 2

, MySQL , ILIKE. LIKE. , ILIKE LIKE PostgreSQL, RDBMS .

LOWER :

.where('LOWER(name) LIKE LOWER(?)', "%#{agency_name}%")

@mu .