Implementing Case-Insensitive Sorting in Rails using PostgreSQL

In case you have established the database with a different collation, it is possible to utilize an alternate collation in the query. You can view all the collations available by executing the following command. Additionally, I forgot to mention that ‘a11’ should precede ‘a2’. The comparison of strings in PostgreSQL is influenced by the chosen locale and collation.


Solution 1:

result = Users.find(:all, :order => "LOWER(name)")

To borrow elements from Brad and Frank’s approaches.


Solution 2:


The response given by LanecH has been modified to suit Rails 3 or higher, which includes Rails 4 and 5.

users = User.order('LOWER(name)')

Alternatively, you can generate a named scope that is reusable.

class User < ActiveRecord::Base
  scope :order_by_name, -> { order('LOWER(name)') }
end
users = User.order_by_name


Solution 3:


If you are using the accepted answer, it is likely that a warning will be issued with Rails 5.2.

A warning of deprecation has been issued due to a query method that is deemed dangerous. This method uses its arguments as raw SQL and has been called with non-attribute argument(s) of “LOWER(?) ASC”.

A possible solution is to depend on Arel, which has now been incorporated into Rails.

results = User.order(User.arel_table['name'].lower.asc)
# results.to_sql == "SELECT "users"".* FROM ""users"" ORDER BY LOWER(""users"".""name"") ASC""

Frequently Asked Questions