'Postgres accent insensitive LIKE search in Rails 3.1 on Heroku

How can I modify a where/like condition on a search query in Rails:

find(:all, :conditions => ["lower(name) LIKE ?", "%#{search.downcase}%"])

so that the results are matched irrespective of accents? (eg métro = metro). Because I'm using utf8, I can't use "to_ascii". Production is running on Heroku.

ruby-on-railspostgresqlrails-postgresqlunaccent


Solution 1:[1]

For those like me who are having trouble on add the unaccent extension for PostgreSQL and get it working with the Rails application, here is the migration you need to create:

class AddUnaccentExtension < ActiveRecord::Migration
  def up
    execute "create extension unaccent"
  end

  def down
    execute "drop extension unaccent"
  end
end

And, of course, after rake db:migrate you will be able to use the unaccent function in your queries: unaccent(column) similar to ... or unaccent(lower(column)) ...

Solution 2:[2]

First of all, you install postgresql-contrib. Then you connect to your DB and execute:

CREATE EXTENSION unaccent;

to enable the extension for your DB.

Depending on your language, you might need to create a new rule file (in my case greek.rules, located in /usr/share/postgresql/9.1/tsearch_data), or just append to the existing unaccent.rules (quite straightforward).

In case you create your own .rules file, you need to make it default:

ALTER TEXT SEARCH DICTIONARY unaccent (RULES='greek');

This change is persistent, so you need not redo it.

The next step would be to add a method to a model to make use of this function.

One simple solution would be defining a function in the model. For instance:

class Model < ActiveRecord::Base
    [...]
    def self.unaccent(column,value)
        a=self.where('unaccent(?) LIKE ?', column, "%value%")
        a
    end
    [...]
end

Then, I can simply invoke:

Model.unaccent("name","text")

Invoking the same command without the model definition would be as plain as:

Model.where('unaccent(name) LIKE ?', "%text%"

Note: The above example has been tested and works for postgres9.1, Rails 4.0, Ruby 2.0.

UPDATE INFO
Fixed potential SQLi backdoor thanks to @Henrik N's feedback

Solution 3:[3]

There are 2 questions related to your search on the StackExchange: https://serverfault.com/questions/266373/postgresql-accent-diacritic-insensitive-search

But as you are on Heroku, I doubt this is a good match (unless you have a dedicated database plan).

There is also this one on SO: Removing accents/diacritics from string while preserving other special chars.

But this assumes that your data is stored without any accent.

I hope it will point you in the right direction.

Solution 4:[4]

Assuming Foo is the model you are searching against and name is the column. Combining Postgres translate and ActiveSupport's transliterate. You can do something like:

Foo.where(
  "translate(
    LOWER(name),
    'âãäå???ÁÂÃÄÅ???èééêë??????????ìíîïì???ÌÍÎÏÌ???óôõö???ÒÓÔÕÖ???ùúûü????ÙÚÛÜ????',
    'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
  )
  LIKE ?", "%#{ActiveSupport::Inflector.transliterate("%qué%").downcase}%"
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Edison Machado
Solution 2
Solution 3 Community
Solution 4 Christian Fazzini

Related Questions