Feature Request: Case-insensitive sorting in Ransack for SQLite databases

[chriskrams]

Summary

When using Ransack with SQLite, the generated ORDER BY clauses are case-sensitive by default.
SQLite supports case-insensitive sorting using the COLLATE NOCASE modifier, but there is no built-in Ransack configuration or adapter-level handling for this.

This results in inconsistent sorting compared to other databases (e.g., PostgreSQL with LOWER() or ILIKE support).

---

Steps to Reproduce

1. Create a simple model and seed data:

   class User < ApplicationRecord; end
   User.create!(name: "alice")
   User.create!(name: "Bob")
   User.create!(name: "carol")

2. In a controller:

   @q = User.ransack(params[:q])
   @users = @q.result

3. In the view:

   <%= sort_link @q, :name, "Name" %>

4. With params[:q][:s] = 'name asc', the generated SQL is:

   SELECT "users".* FROM "users" ORDER BY name ASC;

   The results are case-sensitive: ["Bob", "alice", "carol"].

---

Expected Behavior

When using SQLite, sorting should ideally use:

ORDER BY name COLLATE NOCASE ASC;

so that results are case-insensitive: ["alice", "Bob", "carol"].

---

Actual Behavior

Ransack currently generates:

ORDER BY name ASC;

which sorts capitalized names before lowercase ones.

---

Workaround / Temporary Solution

A working workaround is to patch ActiveRecord::Relation#order in an initializer:

# config/initializers/sqlite_nocase_order.rb
if ActiveRecord::Base.connection.adapter_name == "SQLite"
  module SQLiteNoCaseOrder
    def order(*args)
      transformed_args = args.map do |arg|
        case arg
        when String
          # Parse: "name ASC" oder "name DESC"
          if arg =~ /\A(\w+)\s+(ASC|DESC)\z/i
            column = Regexp.last_match(1)
            dir    = Regexp.last_match(2).upcase
            # Arel.sql um das gesamte Stück SQL
            Arel.sql("(#{column} IS NULL OR #{column} = ''), #{column} COLLATE NOCASE #{dir}")
          else
            # Wenn String nicht erkannt wird, einfach Arel.sql wrap
            Arel.sql(arg)
          end
        when Symbol
          Arel.sql("(#{arg} IS NULL OR #{arg} = ''), #{arg} COLLATE NOCASE ASC")
        when Arel::Nodes::Ordering
          expr = arg.expr
          dir  = arg.ascending? ? "ASC" : "DESC"
          Arel.sql("(#{expr.name} IS NULL OR #{expr.name} = ''), #{expr.name} COLLATE NOCASE #{dir}")
        else
          arg
        end
      end

      super(*transformed_args)
    end
  end

  ActiveRecord::Relation.prepend(SQLiteNoCaseOrder)
end

This ensures that all order calls (including those generated by Ransack) will automatically include COLLATE NOCASE when running on SQLite.

---

Proposed Improvement

• Add an adapter-specific configuration or hook in Ransack for case-insensitive sorting.

• For example:

  Ransack.configure do |c|
    c.case_insensitive_sort = true
  end

  which could automatically append COLLATE NOCASE for SQLite and LOWER() for other databases.

---

Environment

• Ransack version: 4.x
• Rails version: 7.x
• Ruby version: 3.x
• Database adapter: SQLite3

---

Additional Notes

The workaround works well in production, but it would be more robust if Ransack supported this natively through its adapter system (similar to how it already handles different predicate translations per adapter).