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

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

Default Visitor, used by Mysql
Default Visitor, used by Mysql
The PostgreSQL-specific subclass
The PostgreSQL-specific subclass

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: Logo

You are commenting using your 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