Yes, Rails does support case-insensitive queries. Or does it?

[Robb Shecter]

Any opinions? I just wrote this post: http://robb.weblaws.org/2013/12/05/yes-rails-supports-case-insensitive-database-queries/

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 knowldgeable people. But on the other hand there’s nothing in the Arel docs or source code which specify this behavior. I.e. something along the lines of “Arel.match will perform a case-insensitive query, adapting for the particular database in use.” That’d be very reassuring. But in fact, a search for the word case in the comments yields zero results.

[Paul Jungwirth]

That is a nice find! I once submitted a documentation-only pull
request to paperclip to promote a feature from undocumented to
documented. :-)

In the MySQL case whether the query is case sensitive or not actually
depends on the column's collation value.

Paul

> --
> You received this message because you are subscribed to the Google Groups
> "pdxruby" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to pdxruby+u...@googlegroups.com.
> To post to this group, send email to pdx...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/pdxruby/a64f4124-0c6a-4660-8bd0-174f59beba6a%40googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.



--
_________________________________
Pulchritudo splendor veritatis.

[Robb Shecter]

Paul Jungwirth wrote:

That is a nice find! I once submitted a documentation-only pull
request to paperclip to promote a feature from undocumented to
documented. :-)

In the MySQL case whether the query is case sensitive or not actually
depends on the column's collation value.

Thanks! I read that about MySQL but I didn't see anything in the Arel code to account for it. So this could be a piece of cruft which was never fully implemented. Good idea about the pull request: hadn't thought of doing that. 

[Matthew Boeh]

An important caveat: ILIKE doesn't really work with indexes.

"The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example,col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.9 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion."

http://www.postgresql.org/docs/9.2/static/indexes-types.html

If you're going to be using the query frequently, you should create a computed index for "LOWER(fieldname)" and then do "LOWER(fieldname) = ?" or "LOWER(fieldname) LIKE ?".

--

You received this message because you are subscribed to the Google Groups "pdxruby" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pdxruby+u...@googlegroups.com.
To post to this group, send email to pdx...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pdxruby/d7b0f506-e87a-4454-86a9-1fcba6861ea3%40googlegroups.com.