Why doesn't Sphinx show all categories?

Question

Sphinx

Hello! I decided to add search using Sphinx to my Laravel application. I installed the fobiaphp / laravel-sphinx library and figured out the settings. There is a problem with the query, in particular, the output of all categories for a particular product. In Laravel, this request looks like this:

select `categories`.*, `categories_products`.`product_id` as `pivot_product_id`, `categories_products`.`category_id` as `pivot_category_id` 
from `categories` inner join `categories_products` on `categories`.`id` = `categories_products`.`category_id`
 where `categories_products`.`product_id` in (41525, 49423, 49823, 52184, 52553, 52554, 52555, 52556, 52557, 52558, 52559, 52560, 52561, 52562, 52564)

In principle, it copes with its task and displays the categories correctly.

In sphinx, I made a similar request, it works, but for example, where several categories are attached, it displays one at a time. Actually setting up the index in the sphinx:

source categories : conn {
      sql_query =   SELECT cp.product_id, cp.category_id, cp.product_id AS product_id_attr, cp.category_id AS category_id_attr, c.name, c.name AS name \
                         FROM categories AS c \
                    INNER JOIN categories_products AS cp ON c.id = cp.category_id \
      
      sql_attr_uint = product_id_attr
      sql_attr_uint = category_id_attr
      sql_field_string = name
    }

This is how it actually looks like:

select *, product_id_attr as pivot_product_id_attr, category_id_attr as pivot_category_id_attr 
FROM categories 
where product_id_attr in (52842, 52843, 52844, 52850, 53521, 53522, 53523, 53524, 53525, 53526, 53527, 53528, 53529, 53530, 53531)

The categories_products table has only 2 columns: category_id and product_id.

Answer 1

Issue resolved. Updated query for sphinx and fixed bug in laravel. Because swore that id in string comes, therefore, he translated id into integer in the code forcibly.

SELECT c.id AS id, c.name AS name,  \
                GROUP_CONCAT(DISTINCT cp.product_id) AS product_id, \
        GROUP_CONCAT(DISTINCT cp.category_id) AS category_id \
                     FROM categories AS c \
                INNER JOIN categories_products AS cp ON c.id = cp.category_id \
        GROUP BY c.id \

Answer 2

And why do you need a sphinx for this, choose them from a muscle, a sphinx is for another