Yes, Rails does support case-insensitive queries

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:

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s