They’re unfortunately just a little buried and a little undocumented. And so conventional wisdom is that Rails doesn’t do case-insensitive finds. But in fact it does:
Example 1: Find all the statutes whose name begins with texas, case-insensitively:
t = Statute.arel_table
Statute.where(t[:name].matches('texas%'))
Example 2: Same query, using the excellent Squeel gem which makes this a little nicer:
Statute.where{ name =~ 'texas%' }
Results: Both examples generate this SQL when run on MySQL:
SELECT `statutes`.* FROM `statutes` WHERE (`statutes`.`name` LIKE 'texas%')
…but they generate this instead on PostgreSQL:
SELECT "statutes".* FROM "statutes" WHERE "statutes"."name" ILIKE 'texas%'
Here’s where the magic happens
Editorial: Is this a real feature?
Can we code for it and depend on it? I think so.
It’s there in the source code. There are a few sprinkled references by knowledgeable people. But on the other hand there’s nothing in the Arel docs or source code which specifies this behavior. I.e. something like “Arel.match will perform a case-insensitive query, adapting for the particular database in use.” That’d be very reassuring. And so I’ve submitted that as a pull request.
Update: My pull request documenting the feature was accepted, so hopefully knowledge about it will spread.
Dogweather 