Search code examples
yii2sphinx

How to count all records if use alias in select query?

I use Sphinx with Yii2 and need to query with filter by jSON field.

$query = new \yii\sphinx\Query();
$query->from('announcements');
$query->addSelect("*");
$query->addSelect(new Expression("IN(filters['color'], 'blue', 'red', 'green') AS f_color"));
$query->where("is_active = 1");
$query->andWhere("f_color = 1");
$announces = $query->all();

There is jSON field filters in my Sphinx index. For example:

[filters] => {"brand":"Toyota","model":"Prius","color":"red","price":"12000"... etc]

It works OK. But now I need to make a pagination... and there is a problem when I try to count records before $query->all()

$count = $query->count(); // Return error "no such filter attribute 'f_color'"

Generated query was:

SELECT COUNT(*) FROM announcements WHERE ( is_active = 1 ) AND ( f_color = 1 )
Solution

  • count() by default replaces the select part with * and this is where your alias is defined hence the error.

    There are different ways to achieve it like:

    • use ActiveDataProvider like described here,
    • use META information like described here

    Since you want to make a pagination I would go with the first example.