How to properly implement property filters in Sphinx?

Question

Sphinx

I am rewriting the bulletin board site from mySQL to SphinxSE - I am doing it on the Yii2 framework (yii \ sphinx \ Query)
I will describe the essence of the issue using a simplified example ...

In mySQL DB. there are tables `announces` and `options`, the first contains announcements (title, text, submission date, etc.), the second contains properties (property_name, value), one-to-many relationship:
`announces`.`id` -> `options`.ann_id

In Sphinx index settings I did this:

source announces
{
     sql_query	  = \
        SELECT `announces`.`id`, `announces`.`header`, `announces`.`description`, \ 
       (SELECT JSON_OBJECTAGG(`option_id`, `value`) FROM `options` WHERE `options`.`ann_id` = `announces`.`id`) AS `filters`, \
       и т.д.
      
      sql_field_string = header
      sql_field_string = description 
      sql_attr_json = filters
}

As a result, I get a field in jSON format in the `announces` index - like this:

[filters] => {"422":"Toyota","423":"Prius","424":"Гибрид","425":"Автомат","426":"Передний","427":"Хэтчбэк","429":"Серый","430":1800,"431":2019]

Now I can filter by properties, like this:

$query = new Query(); // Sphinx
        $query->from('announces')
              ->where(['disabled' => 0])
             ->andWhere( "filters['424'] = 'Гибрид'" )
            ->all();

So I get what I need...
And now the problem... I need to use IN to check for the presence of one of the options in jSON
So, I tried the option:

->andWhere( " filters['424'] IN ( 'Бензин', 'Дизель', 'Газ' ) ") // Так не ищет в jSON :(

Another option is to use under the OR loop inside IN - but I don't like it... There are a lot of filters - this is a simplified example...

Answer 1

In the original , IN is used as a function, try it.

Answer 2

Sergei Tikhonov , yes, thanks!
It works. So:

$query = new Query(); // Sphinx
$query->from('announces');
$query->addSelect("*");
$query->addSelect(new Expression("IN(filters['424'], 'Бензин', 'Дизель', 'Газ') AS f_424"));
$query->andWhere("f_424 = 1");
$query-all();

... everything is OK
But now a new problem... - with pagination pagination...
Before calling $query->all() , you need to calculate the total number and add limit and offset
Trying to calculate $total = $query-> count(); - gives an error " no such filter attribute 'f_425' ", because the query it executes is this: SELECT COUNT(*) FROM `announcements` WHERE ...... AND (f_425 = 1)