Correct Alphabetical Order - platform-wide solution

Question

Currently in our app we do not override the system’s default method for alphabetizing data, it will sort capital letters over lowercase letters.

How to enable case-insensitive across the application without changing the collation?

Database: PostgreSQL

Any idea on this ?

Answer 1

A quick and dirty work-around could be adding a scope like this to your ApplicationRecord:

scope :order_ci, ->(attribute) { order("LOWER(#{attribute})") }

Or like this using a specific collate:

scope :order_ci, ->(attribute) { order("#{attribute} COLLATE en_US.UTF-8") }

And then use that scope like this Foo.order_ci(:name) instead of Foo.order(:name).

But keep in mind that this “solution” has many flaws: The order method that comes with Ruby on Rails accepts many data types and formats (single values, hashes, strings, lists) and not only one but many attributes at the same time. And the Rails method sanitized the input correctly, this method does not.

Which means, when you want to use such a work-around instead of fixing your database's collation. Then you need to make sure that your overridden method is able to basically handle all data formats and does proper sanitization as the Ruby on Rails method would do. Otherwise, you risk bugs in the best case and security vulnerabilities in the worst case.