How to implement custom sorting on Sphinx?

Question

Sphinx

Greetings!

Essence:
There is an index of product offers, in which, in addition to the basic data for faceted and full-text search, there are JSON format attributes:
1) Attribute with the name "similar_from". This attribute contains an array of IDs of similar products for which this product is similar .

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

Here you can see that:

• product with ID:1 is similar for products ID:2, ID:3
  
• product with ID:2 is similar for products ID:1, ID:3
  
• product with ID:3 is similar for products ID:1, ID:2
  

2) An attribute with the name "similar". This attribute contains an array of IDs of similar products for this product

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

In fact, this is some kind of data inversion of the similar_from attribute.
With an important clarification: the order is extremely important here, i.e. the lower the index of an array element, the more similar it is to a specific product. For example, ID:3 is more similar to product ID:1 than ID:2. ( The
collections themselves ( array similar ) of similar products are built by a special backend algorithm that calculates the distances between products) similar array


. Moreover, it is important not to lose the ability to be able to filter, do pagination, other sorting (by other index fields, in this case sorting in order from the similar array is not needed), etc. all the basic sphinx goodies?

Answer 1

You can try this: for each "similar" product, write a json dictionary containing the id of the product it looks like as a key, and the position in the "similar" product of the corresponding product as a value.
Then to get similar to the product with id=1 there will be something like

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

in fact, the amount of data stored in JSON doubles: previously they stored a list of id similar, and now also the position of each similar.