Аналогичные строки UTF-8 для поля автозаполнения

Фон

Пользователи могут вводить имя, и система должна соответствовать тексту, даже если пользовательский ввод или поле базы данных содержат символы с акцентом (UTF-8). Это использует модуль pg_trgm.

Проблема

Код напоминает следующее:

  SELECT
    t.label
  FROM
    the_table t
  WHERE
    label % 'fil'
  ORDER BY
    similarity( t.label, 'fil' ) DESC

Когда пользователь набирает fil, запрос соответствует filbert, но не filé powder. (Из-за акцентированного характера?)

Неудачное решение №1

Я попытался реализовать функцию unaccent и переписать запрос как:

  SELECT
    t.label
  FROM
    the_table t
  WHERE
    unaccent( label ) % unaccent( 'fil' )
  ORDER BY
    similarity( unaccent( t.label ), unaccent( 'fil' ) ) DESC

Это возвращает только filbert.

Неудачное решение №2

Как было предложено:

CREATE EXTENSION pg_trgm;
CREATE EXTENSION unaccent;

CREATE OR REPLACE FUNCTION unaccent_text(text)
  RETURNS text AS
$BODY$
  SELECT unaccent($1); 
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 1;

Все остальные индексы в таблице были удалены. Тогда:

CREATE INDEX label_unaccent_idx 
ON the_table( lower( unaccent_text( label ) ) );

Это возвращает только один результат:

  SELECT
    t.label
  FROM
    the_table t
  WHERE
    label % 'fil'
  ORDER BY
    similarity( t.label, 'fil' ) DESC

Вопрос

Каков наилучший способ переписать запрос, чтобы гарантировать, что оба результата будут возвращены?

Спасибо!

Похожие

http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Unaccent_filtering_dictionary

http://postgresql.1045698.n5.nabble.com/index-refuses-to-build-td5108810.html

Ответы

Ответ 1

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

CREATE INDEX label_Lower_unaccent_trgm_idx
ON test_trgm USING gist (lower(unaccent_text(label)) gist_trgm_ops);

Первоначально у меня был индекс GIN, но позже я узнал, что GiST, вероятно, даже лучше подходит для такого типа запросов, потому что он может возвращать значения, отсортированные по подобию. Подробнее:

Ваш запрос должен соответствовать выражению индекса, чтобы иметь возможность использовать его.

SELECT label
FROM   the_table
WHERE  lower(unaccent_text(label)) % 'fil'
ORDER  BY similarity(label, 'fil') DESC -- it ok to use original string here

Однако "filbert" и "filé powder" на самом деле не очень похожи на "fil" в соответствии с оператором%. Я подозреваю, что вы действительно этого хотите:

SELECT label
FROM   the_table
WHERE  lower(unaccent_text(label)) ~~ '%fil%'
ORDER  BY similarity(label, 'fil') DESC -- it ok to use original string here

Здесь будут найдены все строки, содержащие строку поиска, и сначала отсортируйте лучшие совпадения в соответствии с оператором %.

И сочная часть: выражение может использовать индекс GIN или GiST, поскольку PostgreSQL 9.1! Я цитирую руководство на pu_trgm moule:

Начиная с PostgreSQL 9.1, эти типы индексов также поддерживают индекс ищет LIKE и ILIKE, например

Если вы действительно использовали оператор %:

Вы пробовали понизить порог для оператора подобия % с помощью set_limit():

SELECT set_limit(0.1);

или даже ниже? Значение по умолчанию - 0,3. Просто, чтобы узнать, является ли его порог, который фильтрует дополнительные совпадения.

Ответ 2

Решение для PostgreSQL 9.1:

-- Install the requisite extensions.
CREATE EXTENSION pg_trgm;
CREATE EXTENSION unaccent;

-- Function fixes STABLE vs. IMMUTABLE problem of the unaccent function.
CREATE OR REPLACE FUNCTION unaccent_text(text)
  RETURNS text AS
$BODY$
  -- unaccent is STABLE, but indexes must use IMMUTABLE functions.
  SELECT unaccent($1); 
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 1;

-- Create an unaccented index.
CREATE INDEX the_table_label_unaccent_idx
ON the_table USING gin (lower(unaccent_text(label)) gin_trgm_ops);

-- Define the matching threshold.
SELECT set_limit(0.175);

-- Test the query (matching against the index expression).
SELECT
  label
FROM
  the_table
WHERE
  lower(unaccent_text(label)) % 'fil'
ORDER BY
  similarity(label, 'fil') DESC

Возвращает "filbert", "fish fillet" и "filé powder".

Без вызова SELECT set_limit(0.175); вы можете использовать оператор double tilde (~~):

-- Test the query (matching against the index expression).
SELECT
  label
FROM
  the_table
WHERE
  lower(unaccent_text(label)) ~~ 'fil'
ORDER BY
  similarity(label, 'fil') DESC

Также возвращает "filbert", "fish fillet" и "filé powder".