Sphinx facet not work with json attribute when use QueryBuilder

[InteLigent]

use php example:

$facets = ['attr.fg_3'];
foreach ($filter_groups as $ftg) {
    $facets[] = 'attr.fg_'.$ftg['filter_group_id'];
}
$query->facets($facets);

Final SphinxQL:

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected '.', expecting $end near '.`fg_3` AS `attr_fg_3` ; SHOW META'
The SQL being executed was: SELECT `id` FROM `product` WHERE category_id = 72 ORDER BY `date_modified` DESC LIMIT 16 FACET `attr`.`fg_3` AS `attr_fg_3` ; SHOW META

[klimov-paul]

What whould be valid SphinxQL for your case?

[InteLigent]

I have been edit subject above.

Is correct SphinxQL:

SELECT id FROM product WHERE category_id = 72 ORDER BY date_modified DESC LIMIT 16 FACET attr.fg_3 AS attr_fg_3 ; SHOW META

[klimov-paul]

You should use yii\db\Expression for your case:

$query->facets([
    'jsonAttr' => [
        'select' => [new \yii\db\Expression('attr.fg_1')]
    ],
]);

[InteLigent]

I get following error:

PHP Notice – yii\base\ErrorException

Undefined index: jsonAttr

at line 228 in file .../vendor/yiisoft/yii2-sphinx/Query.php

[klimov-paul]

There must be correspondance between facet name and select expression.
So you shuld either name facet 'attr.fg_1':

$query->facets([
    'attr.fg_1' => [
        'select' => [new \yii\db\Expression('attr.fg_1')]
    ],
]);

Or use alias for selected expression with facet name:

$query->facets([
    'jsonAttr' => [
        'select' => [new \yii\db\Expression('attr.fg_1 AS jsonAttr')]
    ],
]);

Still there is a bug, which does not respects camel-case facet names.
Fix is on the way.

[klimov-paul]

Migrated to yiisoft/yii2-sphinx#21

[klimov-paul]

Fix and doc updates:
yiisoft/yii2-sphinx@0d53300