Как выполнить фильтр по полю типа SET в Sphinx?

389

10 июня 2017, 14:12

В таблице есть поле типа SET. Пример foo,bar,baz. В одном из запросов для индекса у меня JOIN двух таблиц с CONCAT этого поля, что в результате приводит к такому результату foo,bar,foo,baz. То есть, в некоторых случаях, значения в поле могут дублироваться (два раза foo).

Задача: отфильтровать результаты поиска по одному из значений для этого поля. Например, вывести все результаты в которых это поле содержит значение foo. Всегда выборка только по одному значению.

Все что я смог найти хоть чуть-чуть похожее на требуемое мне, это опция sql_attr_multi. Но я так и не смог понять как мне заставить ее работать, так как она ожидает uint, bigint или timestamp перечисленные через запятую, а у меня string. То есть ну ни как не состыковывается.

По идее можно было бы значение этого поля пропустить через SPLIT -> CRC32 -> CONCAT и уже это использовать в индексе. То есть получаются числовые хешсуммы перечисленные через запятую. Но MySQL не позволяет провернуть такое преобразование. Только через хранимые процедуры, а мне этот вариант крайне нежелателен.

Answer 1

Нашел решение. Немного костыльное, но рабочее.

В запросе нужно перебрать все возможные значения для SET, выполнить для них CRC32() и CONCAT().

Выглядит это примерно так

CONCAT(
    -- '0', -- нет необходимости прописывать ведущий ноль
    -- не смотря на то что в результате конкатенации строк,
    -- она может начинается с запятой, Sphinx проглатывает и так
    IF(
        FIND_IN_SET('foo', types) > 0, -- колонка содержит значение
        CRC32('foo'),
        ''
    ),
    ',',
    ... -- повторяем по аналогии для остальных значений SET
) AS types

Пример запроса

sql_query = \
    SELECT \
        id, \
        title, \
        text, \
        CONCAT( \
            IF(FIND_IN_SET('foo', types) > 0, CRC32('foo'), ''), \
            ',', \
            IF(FIND_IN_SET('bar', types) > 0, CRC32('bar'), ''), \
            ',', \
            IF(FIND_IN_SET('baz', types) > 0, CRC32('baz'), '') \
        ) AS types \
    FROM \
        news \
    WHERE \
        enabled = TRUE

и прописываем атрибут для поля

sql_attr_multi = uint types from field

В случае JOIN и объединения полей у нас в условие просто добавляется AND

CONCAT(
    IF(
        FIND_IN_SET('foo', a.types) > 0 AND FIND_IN_SET('foo', b.types) > 0,
        CRC32('foo'),
        ''
    ),
    ',',
    ...
) AS types

Проблема такого подхода в том, что запрос получается просто монструозный. Для одного из индексов у меня, и так не маленький запрос, разросся до 120 строк.

И вторая проблема, это конечно явное прописывание значений для SET. Любое изменение в SET требует правки запросов в конфиге Sphinx-а.

PS: CRC32() можно заменить на порядковый номер значения в SET, но я не советую так делать, так как можно нарушить целостность.