Case Insensitive Sorting in Postgresql

[shingz96]

I would like to ask that is there any possibility to have a way so that can be put in configuration to make the sorting order be done in case insensitive way in Postgresql SQL?

[scarroll32]

@shingz96 does *_i_cont work for you?

See https://github.com/activerecord-hackery/ransack#search-matchers

[shingz96]

that is for searching, what I am looking for is sorting

[lucashungaro]

@shingz96 Hi! Did you find any workarounds for this? I'm thinking about monkey patching Ransack to be able to do something like ORDER BY LOWER(field_name)

[shingz96]

@shingz96 Hi! Did you find any workarounds for this? I'm thinking about monkey patching Ransack to be able to do something like ORDER BY LOWER(field_name)

I did found a possible way from this stackoverflow answer, but take note that this may break query when using DISTINCT,
so i assume there is no other way but manually order by lowercase instead

https://stackoverflow.com/a/34677378

[lucashungaro]

@shingz96 Ah, right! That's along the lines of what I was considering. Thank you for replying!

[scarroll32]

Some more information here about this topic https://www.postgresql.org/docs/13/citext.html

[scarroll32]

Copying from the SO answer https://stackoverflow.com/a/34677378

You can solve this for all string columns of a model at once by using this approach:

lib/ransack_object.rb

module RansackObject

  def self.included(base)
    base.columns.each do |column|
      if column.type == :string
        base.ransacker column.name.to_sym, type: :string do
          Arel.sql("lower(#{base.table_name}.#{column.name})")
        end
      end
    end
  end

end

Then include the ransack object in your model:

class UserWithManyAttributes < ActiveRecord::Base

  include RansackObject

end

[scarroll32]

Updated on the README https://github.com/activerecord-hackery/ransack#case-insensitive-sorting-in-postgresql

[lucashungaro]

Thanks @seanfcarroll!

[sspread]

This solution applies 'lower' to searching as well. Is there a way to only apply to sorting, handling relations?

[MeterSoft]

This solution applies 'lower' to searching as well. Is there a way to only apply to sorting, handling relations?

You can do something like this and control where you want to do lower search or sort by adding _ci to field name

module RansackObject

  def self.included(base)
    base.columns.each do |column|
      if column.type == :string
        base.ransacker "#{column.name}_ci".to_sym, type: :string do
          Arel.sql("lower(#{base.table_name}.#{column.name})")
        end
      end
    end
  end

end

Model.ransack(s: "name_ci asc")