Sorting non-english characters about ransack HOT 4 CLOSED

I noticed we didn't have the correct sorting for Danish characters in our PostgreSQL database. To fix this, I used the ICU (International Components for Unicode) extension. I chose the da-DK-x-icu collation for Danish language sorting, as it's used in Denmark.

Here’s the migration I added:

class UpdateMyModelNameCollation < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      ALTER TABLE MyModel
      ALTER COLUMN name TYPE varchar COLLATE "da-DK-x-icu";
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE MyModel
      ALTER COLUMN name TYPE varchar;
    SQL
  end
end

Now our system can sort Danish characters correctly.

To see the collations your PostgreSQL installation supports, run:

SELECT collname FROM pg_collation;

from ransack.

@scarroll32 Would this be something interesting to add to the documentation? I can only write the Postgres part or maybe just give users a lead on what could be the problem

from ransack.

@davideluque thank you for opening this issue, it is indeed a problem across many different languages.

Can you think of a way to make it language independent? ie: is the only way to do this in Postgres is by adding the collation?

  ALTER COLUMN name TYPE varchar COLLATE "da-DK-x-icu";

from ransack.

@scarroll32, database-level sorting seems to be the most efficient approach. While we could resort to Ruby's Array Sorting, it's not scalable for larger datasets.

Upon investigating collations, I found that they're specific to DBMS.

I expect this issue to guide users and I wonder if there is something else we can do to help.

from ransack.