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

Как я могу изменить условие где / как в поисковом запросе в Rails:

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

чтобы результаты соответствовали независимо от акцентов? (например, метро = метро). Поскольку я использую utf8, я не могу использовать "to_ascii". Производство работает на Heroku.

Источник

5 ответов

Решение

Бедное мужское решение

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

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 и индексах:

Источник

Для таких, как я, которые испытывают трудности при добавлении 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)) ...

Источник

Прежде всего, вы устанавливаете 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 благодаря отзывам @Henrik N

Источник

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

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

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

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

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

Источник

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

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