Rails MySQL ILIKE query

Question

My syntax is off. I want to create a scope by_name that finds all agencies whose name attribute contains the passed in 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 I type in agencies = Agency.by_name("foo"). Here is the query generated:

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

Here is the error message:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '%foo%')

Answer 1

I think it should be:

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

It is in PostgreSQL the keyword ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

In MySQL you do not have ILIKE. Checkout MySQL docs on string comparison functions.

---

Bonus - you can also use Arel. Take a look:

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