Пример реализации autocomplete с использованием FTS движка PostgreSQL tsearch217.08.2016 13:18

Введение

Когда-то давно я написал для себя ежедневник для ведения дел, заметок и фиксации движения по разным задачам. Сделан он был изначально на связке PHP + Kohana 2 + PostgreSQL. Со временем я переписал все на Yii (первой и тогда единственной версии). Для полнотекстового поиска был задействован встроенный в PostgreSQL движок tsearch2. Много лет я пользовался системой, понемногу ее развивал и пришел к тому, что объем текстов в ней накопился приличный. Поиском приходится пользоваться весьма часто и для повышения его удобства я задумал прикрутить к нему autocomplete из состава пакета JQuery UI.

Реализация

Чтобы все было правильно, выбор подсказок должен основываться на том же индексе, что и сам поиск. Все тексты у меня хранятся в отдельной таблице под названием «texts». Вот ее структура:

                                         Table "public.texts"
   Column    |            Type             |                        Modifiers                         
-------------+-----------------------------+----------------------------------------------------------
 txt_id      | bigint                      | not null default nextval(('gen_txt_id'::text)::regclass)
 user_id     | integer                     | not null
 txt         | text                        | not null
 fti_txt     | tsvector                    | 
 last_update | timestamp without time zone | default now()
 format      | textformat                  | default 'wiki'::textformat
Indexes:
    "texts_pkey" PRIMARY KEY, btree (txt_id)
    "texts_txt_id_key" UNIQUE CONSTRAINT, btree (txt_id)
    "fti_texts_idx" gist (fti_txt)
    "last_update_idx" btree (last_update)
    "texts_uid_idx" btree (user_id)

Для реализации задачи формирования списка подсказок по текущей строке поиска был написан Action в виде отдельного, подключаемого действия. Исходник protected/extensions/actions/SearchAutocompleteAction.php:

user->id;
        $_model = new $this->model;
        $_tableName = $_model->tableName();

        // Разбиваем поисковый запрос на слова, отделяем от него последнее слово
        // и сохраняем отдельно это слово и остальной запрос
        $_query_array = explode(' ', Yii::app()->db->quoteValue($_GET['term']));
        $_word = array_pop($_query_array);
        $_preQuery = implode(' ', $_query_array);
        $_suggestions = array();

        /*
         * Запрос получения tsvector из нужных нам записей. Набор записей должен принадлежать текущему пользователю
         * и в него входят только записи, соответствующие первой части запроса (без последнего слова).
         */
        $_sub_sql = "SELECT $this->fts_field FROM $_tableName WHERE user_id=''$_uid''";
        if (count($_query_array) > 0)
            $_sub_sql .= " AND $this->fts_field @@ to_tsquery(''russian'', ''$_preQuery'')";

        /*
         * Окончательный запрос, возвращающий список слов, для дополнения последнего слова запроса.
         * Используется функция ts_stat из tsearch2. Она возвращает список слов в записях, выбранных подзапросом выше,
         * отсортированный по убыванию частоты появления слов в текстах. Можно добавить в сортировку аттрибут ndoc, описывающий
         * количество документов, где встречается слово.
         */
        $_sql = "SELECT word AS $this->attribute FROM ts_stat('$_sub_sql') WHERE word LIKE '$_word%' ORDER BY nentry DESC LIMIT 15;";

        foreach(Yii::app()->db->createCommand($_sql)->query() as $_m)
            $_suggestions[] = count($_query_array) > 0 ? $_preQuery.' '.$_m[$this->attribute] : $_m[$this->attribute];

        echo CJSON::encode($_suggestions);
    }
}

Для разбора алгоритма действий привожу пример SQL запроса по строке поиска «привет хаб», формируемого Action-ом:

SELECT 
    word AS txt 
FROM 
    ts_stat('SELECT fti_txt FROM texts WHERE user_id=''1'' AND fti_txt @@ to_tsquery(''russian'', ''привет'')') 
WHERE 
    word LIKE 'хаб%' 
ORDER BY nentry DESC 
LIMIT 15;

Суть работы tsearch2 в общем заключается в формировании записи типа tsvector в добавок к текстовой, в нашем примере это поле fti_txt. В нее записываются слова текста с указанием их позиций и числа их появления в тексте. По этой записи строится индекс (gin или gist) и в дальнейшем выполняется поиск. Для отладки и мониторинга состояния индекса в tsearch2 есть функция ts_stat. В качестве параметра она принимает текст SQL запроса, возвращающего набор полей типа tsvector. По этому набору строится статистика в виде списка слов с указанием количества вхождений (nentry) и количества документов (записей) где слово встречается (ndoc).

В моем примере если слово в поисковом запросе одно — выполняется поиск похожих на него во всех записях пользователя. Если слов в запросе несколько — последнее слово изымается из запроса, набор записей ограничивается полнотекстовым поиском по первой части запроса (без последнего слова).

Подключение к проекту

Эта часть является Yii 1 специфичной, никакой магии тут нет. Приводится для целостности заметки. Всего будет два шага. Шаг первый — подключение Action-а к контроллеру, в моем случае DiaryController. Для этого в его метод actions () добавляем строки:

    public function actions()
    {
        return array(
            ...
            'acsearch' => array(
                'class' => 'application.extensions.actions.SearchAutocompleteAction',
                'model' => 'Texts',
                'attribute' => 'txt',
                'fts_field' => 'fti_txt',
            ),
            ...
        );
    }

Теперь в соответствующем view заменяем старое текстовое поле поиска:

sh, array('size' => 60,'maxlength' => 255)); ?>

на JQuery UI виджет:

        widget('zii.widgets.jui.CJuiAutoComplete', array(
		'attribute'=> 'sh',
		'sourceUrl' => array('acsearch'),
		'name' => 'sh',
                'value' => $search->sh,
		'options' => array(
			'minLength' => '2',
		),
		'htmlOptions' => array(
			'size' => 60,
			'maxlength' => 255,
		),
	)); ?>

В результате получим нечто, похожее на картинку:

Недостатки

У всей системы есть один крупный недостаток — слова в поле типа tsvector записываются после стемминга. Проще говоря у большинства слов «отрезаются» окончания для учета в поиске их различных форм. Посмотрите на картинку выше и обратите внимание на слово «формирован». Таким образом данное решение применимо в проектах для личного/внутреннего использования. Без решения данной проблемы показывать такое людям нельзя. Возможно у кого-нибудь найдется достойное решение или хотя бы мысль. Добро пожаловать в комментарии.

Комментарии (10)

• oxidmod

  17 августа 2016 в 10:10

  0

  ↑

  ↓

  еластик серч/сфинкс?

  • ischerbin

    17 августа 2016 в 10:43

    0

    ↑

    ↓

    tsearch2 встроен в базовод, не надо ничего отдельно ставить, настраивать и запускать. Но в любом случае на вкус и цвет…

    • oxidmod

      17 августа 2016 в 10:55

      0

      ↑

      ↓

      вы же спрашивали какбы подфиксать окончания? это был ответ

      • ischerbin

        17 августа 2016 в 10:59

        0

        ↑

        ↓

        Это как ответить на вопрос
         — Как пропатчить KDE под FreeBSD?
         — Ставь винду!
        Вроде бы и ответ и даже должно сработать, но чего то все-таки не хватает.

        • oxidmod

          17 августа 2016 в 11:11 (комментарий был изменён)

          –2

          ↑

          ↓

          >> Возможно у кого-нибудь найдется достойное решение или хотя бы мысль. Добро пожаловать в комментарии.

          нужно было написать тогда: Возможно у кого-нибудь найдется достойное решение или хотя бы мысль. Добро пожаловать в комментарии. (но так чтобы особо не напрягаться)

          на этом позвольте откланяться

• Shtucer

  17 августа 2016 в 11:17

  0

  ↑

  ↓

  Я, конечно, ничего не понимаю в этих ваших полнотекстовых поисках, поэтому у меня вопрос на вопрос: зачем вы выбираете fts_txt вместо text? В автокомплит подавать кусок живого текста из документа, а не индекс по нему. Это несколько усложнит запрос, но решит проблему с окончаниями. Или так не принято в полнотекстовом поиске делать?

  • ischerbin

    17 августа 2016 в 11:22

    0

    ↑

    ↓

    ts_stat по сути не предназначена для решения прикладных задач, она задумана для целей отладки и мониторинга. Она выдает статистику по тому, какие слова как часто и где встречаются. В качестве параметра она принимает текст SQL запроса, возвращающего набор полей типа tsvector, только так она сработает. В противном случае будет:

    ERROR:  ts_stat query must return one tsvector column
    

    • Shtucer

      17 августа 2016 в 11:37

      0

      ↑

      ↓

      Ну так и пройти немного дальше и использовать полученный tsvector для извлечения информации из текста что-то мешает? Иначе это не было полнотекстовым поиском, а всего лишь стеммером. Ну, так мне кажется. По крайней мере в документации описан довольно богатый арсенал функций, и даже есть раздел «Simple Search Engine», который начинается со слов: Building a search engine involves only a few improvements upon the rudimentary vector searches described in the last section. А в этом самом last section и рассматривался ваш случай — ВЫБРАТЬ tsvector.

• Melkij

  17 августа 2016 в 11:51

  +2

  ↑

  ↓

  Использовать Yii и так грубо допускать SQL-инъекции? Вроде бы за такое уже больно били по рукам до появления Yii.

  • ischerbin

    17 августа 2016 в 12:03

    +1

    ↑

    ↓

    Спасибо! Заменил строку:

    $_query_array = explode(' ', $_GET['term']);
    

    
    на:

    $_query_array = explode(' ', Yii::app()->db->quoteValue($_GET['term']));