Not case sensitive search with active record

Question

I use rails 3.0.4

here is a piece of Arel query in my rails application, How can I use the upcase method to make a none case sensitive search in a database agnostic way?

Customer.where("company_id = ? and (firstname like ? or lastname like ? or reference like ?)", current_user.company_id, "%#{params[:query]}%", "%#{params[:query]}%", "%#{params[:query]}%")

Thanks

Answer 1

Here are a couple of options for you.

First, LIKE is already case-insensitive, but for Postgres you'll have to use ILIKE to make your searches case-insensitive.

Customer.where('firstname LIKE ?', "%john%").first.name
=> 'John'

Second, if you want to compare using case-insensitivity with non-pattern-matching comparisons, like <>, =, <=, etc. then you can use the COLLATE command to impose case-insensitive matches:

Customer.where('firstname COLLATE utf8_unicode_ci = ?', 'john').first.name
=> 'John'

It seems that Postgres does not have a COLLATE command yet, but you can view more about case-insensitive search options here. In general when you want to perform pattern matching or complex queries you aren't going to be able to do so in a database-agnostic way. My recommendation is to use a single database system in both development and production. This ensures that your queries will also behave the same way in both environments which should lead to fewer bugs. If you do find the need to support multiple database systems then your best option is to simply create two different queries - one to be run on MySQL and one to be run on Postgres for example.