Postgres акцент нечувствителен LIKE поиск в Rails 3.1 на Heroku

Ответ 1

Бедственное решение

Если вы можете создать функцию, вы можете использовать ее. Я собрал список, начинающийся с здесь и добавленный к нему со временем. Это довольно полно. Вы даже можете удалить некоторые символы:

CREATE OR REPLACE FUNCTION lower_unaccent(text)
  RETURNS text AS
$func$
SELECT lower(translate($1
     , '¹²³áàâãäåāăąÀÁÂÃÄÅĀĂĄÆćčç©ĆČÇĐÐèéêёëēĕėęěÈÊËЁĒĔĖĘĚ€ğĞıìíîïìĩīĭÌÍÎÏЇÌĨĪĬłŁńňñŃŇÑòóôõöōŏőøÒÓÔÕÖŌŎŐØŒř®ŘšşșߊŞȘùúûüũūŭůÙÚÛÜŨŪŬŮýÿÝŸžżźŽŻŹ'
     , '123aaaaaaaaaaaaaaaaaaacccccccddeeeeeeeeeeeeeeeeeeeeggiiiiiiiiiiiiiiiiiillnnnnnnooooooooooooooooooorrrsssssssuuuuuuuuuuuuuuuuyyyyzzzzzz'
     ));
$func$ LANGUAGE sql IMMUTABLE;

Ваш запрос должен работать следующим образом:

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

Для поиска с левой привязкой вы можете использовать индекс для функции для очень быстрых результатов:

CREATE INDEX tbl_name_lower_unaccent_idx
  ON fest (lower_unaccent(name) text_pattern_ops);

Для таких запросов, как:

SELECT * FROM tbl WHERE (lower_unaccent(name)) ~~ 'bob%'

Правильное решение

В PostgreSQL 9.1 + с необходимыми привилегиями вы можете просто:

CREATE EXTENSION unaccent;

который предоставляет функцию unaccent(), выполняя то, что вам нужно (кроме lower(), просто используйте это дополнительно, если необходимо). Прочтите руководство об этом расширении.
Также доступен для синтаксиса PostgreSQL 9.0, но CREATE EXTENSION является новым в 9.1.

Подробнее о unaccent и индексах:

• Поддерживает ли PostgreSQL "нечувствительность к акценту" Параметры сортировки?

Ответ 2

Для таких, как я, у кого возникают проблемы с добавлением расширения unaccent для PostgreSQL и его работы с приложением Rails, вот вам миграция, которую вам нужно создать:

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

  def down
    execute "drop extension unaccent"
  end
end

И, конечно, после rake db:migrate вы сможете использовать функцию unaccent в ваших запросах: unaccent(column) similar to ... или unaccent(lower(column)) ...

Ответ 3

Прежде всего, вы устанавливаете postgresql-contrib. Затем вы подключаетесь к своей БД и выполняете:

CREATE EXTENSION unaccent;

чтобы включить расширение для вашей базы данных.

В зависимости от вашего языка вам может потребоваться создать новый файл правил (в моем случае greek.rules, расположенный в /usr/share/postgresql/9.1/tsearch_data) или просто добавить к существующей unaccent.rules (довольно просто).

Если вы создаете свой собственный файл .rules, вам нужно сделать его по умолчанию:

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

Это изменение является постоянным, поэтому вам не нужно его переделывать.

Следующим шагом будет добавление метода к модели для использования этой функции.

Одним простым решением было бы определение функции в модели. Например:

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

Затем я могу просто вызвать:

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

Вызов той же команды без определения модели будет таким же простым, как:

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

Примечание. Вышеприведенный пример был протестирован и работает для postgres9.1, Rails 4.0, Ruby 2.0.

ИНФОРМАЦИЯ ОБНОВЛЕНИЯ
Исправлен потенциал SQLi backdoor благодаря обратной связи @Henrik N

Ответ 4

Есть два вопроса, связанных с вашим поиском на StackExchange: https://serverfault.com/questions/266373/postgresql-accent-diacritic-insensitive-search

Но поскольку вы на Heroku, я сомневаюсь, что это хорошее совпадение (если у вас нет специального плана базы данных).

В SO также есть: Удаление акцентов/диакритических знаков из строки при сохранении других специальных символов.

Но это предполагает, что ваши данные сохраняются без какого-либо акцента.

Надеюсь, он укажет вам в правильном направлении.

Ответ 5

Предполагая, что Foo - это модель, которую вы ищете, и name - это столбец. Объединение Postgres translate и ActiveSupport transliterate. Вы можете сделать что-то вроде:

Foo.where(
  "translate(
    LOWER(name),
    'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
    'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
  )
  LIKE ?", "%#{ActiveSupport::Inflector.transliterate("%qué%").downcase}%"
)