Ransack: Postgres UNACCENT() for cont, not_count, start, not_start...

[maduhaime]

I would like to use the unaccent() extension of Postgres with Ransack. My first take on this was to override DERIVED_PREDICATES but it was an ugly solution and it didn't work.

Use case: A user is searching for décoration (in french) but enter "decor" in the search field. No result will show because ILIKE is case insensitive but not accent insensitive!

Any clue on how I can solve this? How can I contribute?

# Overrides Ransack Constants to use the unaccent() extension of Postgres

module Ransack
  module Constants
    remove_const(:DERIVED_PREDICATES) if (defined?(DERIVED_PREDICATES))

    DERIVED_PREDICATES = [
      ['cont', {:arel_predicate => 'matches', :formatter => proc {|v| "UNACCENT('%#{escape_wildcards(v)}%')"}}],
      ['not_cont', {:arel_predicate => 'does_not_match', :formatter => proc {|v| "UNACCENT('%#{escape_wildcards(v)}%')"}}],
      ['start', {:arel_predicate => 'matches', :formatter => proc {|v| "UNACCENT('#{escape_wildcards(v)}'%)"}}],
      ['not_start', {:arel_predicate => 'does_not_match', :formatter => proc {|v| "UNACCENT('#{escape_wildcards(v)}%')"}}],
      ['end', {:arel_predicate => 'matches', :formatter => proc {|v| "UNACCENT('%#{escape_wildcards(v)}')"}}],
      ['not_end', {:arel_predicate => 'does_not_match', :formatter => proc {|v| "UNACCENT('%#{escape_wildcards(v)}')"}}],
      ['true', {:arel_predicate => 'eq', :compounds => false, :type => :boolean, :validator => proc {|v| TRUE_VALUES.include?(v)}}],
      ['false', {:arel_predicate => 'eq', :compounds => false, :type => :boolean, :validator => proc {|v| TRUE_VALUES.include?(v)}, :formatter => proc {|v| !v}}],
      ['present', {:arel_predicate => 'not_eq_all', :compounds => false, :type => :boolean, :validator => proc {|v| TRUE_VALUES.include?(v)}, :formatter => proc {|v| [nil, '']}}],
      ['blank', {:arel_predicate => 'eq_any', :compounds => false, :type => :boolean, :validator => proc {|v| TRUE_VALUES.include?(v)}, :formatter => proc {|v| [nil, '']}}],
      ['null', {:arel_predicate => 'eq', :compounds => false, :type => :boolean, :validator => proc {|v| TRUE_VALUES.include?(v)}, :formatter => proc {|v| nil}}],
      ['not_null', {:arel_predicate => 'not_eq', :compounds => false, :type => :boolean, :validator => proc {|v| TRUE_VALUES.include?(v)}, :formatter => proc {|v| nil}}]
    ]
  end
end

gem 'rails', '4.0.2'
gem 'pg', '~> 0.17.1'
gem 'ransack', '~> 1.1.0'

[jonatack]

Hi @maduhaime, I do accent-neutral searching like you described with Ransack. You can do it using postgres unaccent in a Ransacker.

In your model, assuming we want to search on a database column called name:

  ransacker :name, type: :string do
    Arel.sql("unaccent(\"name\")")
  end

If you also want to remove punctuation and spaces:

  ransacker :name, type: :string do
    Arel.sql("regexp_replace(unaccent(\"name\"), '[[:punct:][:space:]]', '', 'g')")
  end

You'll need to do a migration to install unaccent:

class AddUnaccentExtension < ActiveRecord::Migration
  def up
    execute 'CREATE EXTENSION IF NOT EXISTS unaccent;'
  end

  def down
    execute 'DROP EXTENSION IF EXISTS unaccent CASCADE;'
  end
end

For performance (fast queries) on frequent search use cases, I've moved to using transliterate on a custom predicate with Ransack to search on an indexed, unaccented (using transliterate) postgres citext (case-insensitive text) search column. Have a look at my answers in recent issues if that interests you.

Hope that helps.

[maduhaime]

Thanks. My knowledge of Postgres (and Ransack) was not good enough. I will read about citex. Next time I will google for special characters... I will probably end here... #332

[jonatack]

Yes, #332 is the one.

Here is a migration to install the citext (PostgreSQL case-insensitive text) extension:

class AddCitextExtension < ActiveRecord::Migration
  def up
    execute 'CREATE EXTENSION IF NOT EXISTS citext;'
  end

  def down
    execute 'DROP EXTENSION IF EXISTS citext CASCADE;'
  end
end

For info, the Rails master branch (future 4.2 release) recently added full citext capability into ActiveRecord here https://github.com/rails/rails/pull/12523 and here https://github.com/rails/rails/commit/3f5339f48e3ce3eb40eb51fb1b686914a719a26a.

citext fields work with 4.0 and 4.1, but you'll see unknown oid warnings in your logfiles. Not a problem, but it's good that citext is fully integrated into AR in Rails 4.2.

[guilpejon]

I had the same problem, and I also wanted to override the cont predicate, so I wouldn't have to touch my views. Here is my solution for this problem, based on #332

# on initializers/ransack.rb
Ransack.configure do |config|
  config.add_predicate 'cont', # Name your predicate
      arel_predicate: 'matches',
      formatter: proc { |s| ActiveSupport::Inflector.transliterate("%#{s}%") }, # Note the %%
      validator: proc { |s| s.present? },
      compounds: true,
      type: :string
end

On user.rb model

ransacker :name, type: :string do
  Arel.sql("unaccent(\"name\")")
end

In some view

<%= f.search_field :name_cont %>

[richardrails]

little correction:

ransacker :name, type: :string do
  Arel.sql("unaccent(name)")
end