How to do case-insensitive order in Rails with postgresql
Ruby on-RailsRubyPostgresqlSql Order-ByCase InsensitiveRuby on-Rails Problem Overview
I am in the process of switching my development environment from sqlite3 to postgresql 8.4 and have one last hurdle.
In my original I had the following line in a helper method;
result = Users.find(:all, :order => "name collate NOCASE")
which provided a very nice case-insensitive search. I can't replicate this for postgresql. Should be easy - any ideas?
Thanks.
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
result = Users.find(:all, :order => "LOWER(name)")
To take a little bit from both Brad and Frank.
Solution 2 - Ruby on-Rails
LanecH's answer adapted for Rails 3+ (including Rails 4 and 5):
users = User.order('LOWER(name)')
Or create a named scope you can reuse:
class User < ActiveRecord::Base
scope :order_by_name, -> { order('LOWER(name)') }
end
users = User.order_by_name
Solution 3 - Ruby on-Rails
Now with Rails 5.2 you probably will get a warning if using the accepted answer.
> DEPRECATION WARNING: Dangerous query method (method whose arguments > are used as raw SQL) called with non-attribute argument(s): "LOWER(?) > ASC".
An alternative could be relying on Arel (it's merged now into Rails):
results = User.order(User.arel_table['name'].lower.asc)
# results.to_sql == "SELECT \"users\".* FROM \"users\" ORDER BY LOWER(\"users\".\"name\") ASC"
Solution 4 - Ruby on-Rails
Have you considered storing your column as citext type? It really just internalizes the call to lower() as I understand it. It would be automatic for you afterwards. If there are times you need a case sensitive search, this may not be the best idea though.
Solution 5 - Ruby on-Rails
IN SQL you could use ORDER BY LOWER(columnname), no idea how to do it in Ruby. A functional index (also on LOWER(columnname) ) will help to speed things up.