Как реализовать кастомную сортировку на Sphinx?

Question

[DenFm]

Приветствую!

Суть:
Имеется индекс товарных предложений, в котором помимо основных данных для фасетного и полнотекствого поиска есть атрибуты формата JSON:
1) Атрибут с наименованием "similar_from". В данном атрибуте лежит массив IDs похожих товаров, для которых данный товар является похожим.

+-------+---------------------------------+
| id    | similar_from                    |
+-------+---------------------------------+
| 1     | [2, 3]                          |
+-------+---------------------------------+
| 2     | [1, 3]                          |
+-------+---------------------------------+
| 3     | [1, 2]                          |
+-------+---------------------------------+

Здесь видно, что:

• товар с ID:1 является похожим для товаров ID:2, ID:3
  
• товар с ID:2 является похожим для товаров ID:1, ID:3
  
• товар с ID:3 является похожим для товаров ID:1, ID:2
  

2) Атрибут с наименованием "similar". В данном атрибуте лежит массив IDs похожих товаров для данного товаров

+-------+---------------------------------+
| id    | similar                         |
+-------+---------------------------------+
| 1     | [3, 2]                          |
+-------+---------------------------------+
| 2     | [3, 1]                          |
+-------+---------------------------------+
| 3     | [2, 1]                          |
+-------+---------------------------------+

По сути, эта некая инверсия данных similar_from атрибута.
С важным уточнением: здесь крайне важна очередность, т.е. чем меньше индекс у элемента массива, тем он более похож на конкретный товар. Например, ID:3 более похож на товар ID:1, чем ID:2.
(Построением самих коллекций (массив similar) похожих товаров занимается специальный алгоритм бекенда, который высчитывает расстояния между товарами)

Вопрос:
Как правильно написать запрос к sphinx (sphinxql), чтобы построить выборку похожих товаров для конкретного товара, с учётом очередности, которая задана в самом массиве similar. Причём, важно не потерять возможность уметь фильтровать, делать пагинацию, другую сортировку (по другим полям индекса, в этом случае сортировка по очередности из массив similar не нужна) и т.д все базовые плюшки sphinx?

Answer 1

[Сергей Тихонов]

Можно попробовать так: для каждого "похожего" товара прописать json-словарь, содержащий в качестве ключа id товара, на который он похож, а в качестве значения - позицию в "похожих" соответствующего товара.
Тогда для получения похожих на продукт с id=1 будет что-то вроде

SELECT id, related_pos.1 AS position WHERE position != 0 ORDER BY position

по-сути объем хранимых в JSON данных увеличивается вдвое: раньше хранили список id похожих, а теперь еще и позицию каждого похожего.

Comments

[DenFm]

Не очень понятно, т.к. 1 товар может быть похожим на десятки других товаров с разными расстоянием похожести, т.е. позиция разная...

[Сергей Тихонов]

Ну вот возьмем ваш пример с колонкой similar.
На первом шаге перенумеруем позиции:

+-------+---------------------------------------+
| id    | similar                               |
+-------+---------------------------------------+
| 1     | {3: 1, 2: 2}                          |
+-------+---------------------------------------+
| 2     | {3: 1, 1: 2}                          |
+-------+---------------------------------------+
| 3     | {2: 1, 1: 2}                          |
+-------+---------------------------------------+

На втором шаге перекинем данные аналогично тому, как это было проделано с колонкой similar_from

+-------+---------------------------------------+
| id    | similar_from                          |
+-------+---------------------------------------+
| 1     | {2: 2, 3: 2}                          |
+-------+---------------------------------------+
| 2     | {1: 2, 3: 1}                          |
+-------+---------------------------------------+
| 3     | {1: 1, 3: 1}                          |
+-------+---------------------------------------+

Получается, раз 1й товар встречался в похожих у 2го и 3го (оба раза на втором месте), то и в новом словаре для него будут ключами - товары 2 и 3, значениями - позиции, оба раза 2-е.

Когда из этого набора данных удастся вытащить позиции для товара 1, получится что-то вроде:

+-------+---------------------------------------+
| id    | similar_from.1                        |
+-------+---------------------------------------+
| 1     | null (или 0, не проверял)             |
+-------+---------------------------------------+
| 2     | 2                                     |
+-------+---------------------------------------+
| 3     | 1                                     |
+-------+---------------------------------------+

Отсюда уже понятно что в where и order by

[DenFm]

Спасибо. Ваше решение рабочие. Применим.