yiisoft / yii2-sphinx

Yii 2 Sphinx extension.

Sphinx Extension for Yii 2

This extension adds Sphinx full text search engine extension for the Yii framework 2.0. It supports all Sphinx features including Real-time Indexes.

For license information check the LICENSE-file.

Documentation is at docs/guide/README.md.

Requirements

At least Sphinx version 2.0 is required. However, in order to use all extension features, Sphinx version 2.2.3 or higher is required.

Installation

The preferred way to install this extension is through composer.

Either run

php composer.phar require --prefer-dist yiisoft/yii2-sphinx

or add

"yiisoft/yii2-sphinx": "~2.0.0"

to the require section of your composer.json.

Configuration

This extension interacts with Sphinx search daemon using MySQL protocol and SphinxQL query language. In order to setup Sphinx "searchd" to support MySQL protocol following configuration should be added:

searchd
{
    listen = localhost:9306:mysql41
    ...
}

To use this extension, simply add the following code in your application configuration:

return [
    //....
    'components' => [
        'sphinx' => [
            'class' => 'yii\sphinx\Connection',
            'dsn' => 'mysql:host=127.0.0.1;port=9306;',
            'username' => '',
            'password' => '',
        ],
    ],
];

yii2-sphinx's Issues

Sphinx

Dear Paul,

Please forgive me - this is off topic but I'm desperate to get this resolved so I can use your great extension.

Could you point me in the right direction to setup Sphinx correctly, this is the error I'm getting for all my effort:

"Can't open shared library '/usr/lib/mysql/plugin/ha_sphinx.so' (errno: 2 /usr/lib/mysql/plugin/ha_sphinx.so: undefined symbol: _ZTI7handler)"

My system is Ubuntu 14 64 Bit, I had followed the instructions here: https://brain.demonpenguin.co.uk/2015/04/30/adding-sphinxse-to-mysql-on-ubuntu-server/

The make seemed to have completed successfully so I got the error from MySQL at the point when I ran "INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

Thank you in advance!

FACET ORDER

You cannot sort an array, because it is not finds the index of the result array

Example:
FACET brand_id ORDER BY COUNT() DESC
// for
$rawFacetResult['value'] = $rawFacetResult[strtolower($facet['value'])];
$facet['value'] == 'brand_id ORDER BY COUNT() DESC' // error notice index

Connection: unknown character

While connecting with the following params:

'components.sphinx' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=127.0.0.1;port=9306;',
    'username' => '******',
    'password' => '******',
],

i got:

Next exception 'yii\db\Exception' with message 'SQLSTATE[HY000] [2054] Server sent charset unknown to the client. Please, report to the developers' in /var/www/vb/vendor/yiisoft/yii2/db/Connection.php:324

when trying to use the following params:

'components.sphinx' => [
    'class' => 'yii\sphinx\Connection',
    'dsn' => 'mysql:host=127.0.0.1;port=9306;',
    'username' => '******',
    'password' => '******',
],

i got

PHP Fatal error: Call to a member function prepare() on null in /var/www/vb/vendor/yiisoft/yii2/db/Command.php on line 153

$ php -v
PHP 5.6.7 (cli) (built: Mar 30 2015 16:49:27)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
with Zend OPcache v7.0.4-dev, Copyright (c) 1999-2015, by Zend Technologies

ActiveQuery::search() fails if facet key does not match select part of facet

Here is an query example, this code tries to find min and max price in index:

$query = Item::find();
$query->addFacets([
    'price_min' => [
        'select' => 'price',
        'order' => ['price' => SORT_ASC],
        'limit' => 1
    ],
    'price_max' => [
        'select' => 'price',
        'order' => ['price' => SORT_DESC],
        'limit' => 1
    ],
]);
$query->search();

It will fail with

yii\base\ErrorException: Undefined index: price_min
/home/pahanini/projects/catalog/vendor/yiisoft/yii2-sphinx/Query.php:234

To solve this issue I have to use the same name for column name and facet key... This code works perfect:

$query = Item::find();
$query->addFacets([
'price_min' => [
'select' => 'price as price_min', // add as here
'order' => ['price' => SORT_ASC],
'limit' => 1
],
'price_max' => [
'select' => 'price as price_max', // add as here
'order' => ['price' => SORT_DESC],
'limit' => 1
],
]);
$query->search();

I suggest to make possible to use different names for key's name and select in this case.

Error while I makes query with MATCH and WHERE

Hello. Sorry for mistakes.

I installed Yii2 and yii2-sphinx from composer.

"yiisoft/yii2": ">=2.0.4",
"yiisoft/yii2-sphinx": "^2.0"

Then I configured sphinx and wrote simple code:

$sphinxQuery = new SphinxQuery();
$result = $sphinxQuery->select('*')
            ->from('services')
            ->match(new Expression(':match', ['match' => '@(description) ' . Yii::$app->sphinx->escapeMatchValue('some value for search')]))
            ->andWhere(['zone_id' => 0])
            ->all();

And after this i got invalid QL:

SELECT * FROM `services` WHERE (MATCH(:'@(description) smallest')) AND (`zone_id`=0)
ERROR 1064 (42000): sphinxql: syntax error, unexpected '(', expecting IDENT (or 5 other tokens) near '(MATCH(:'@(description) smallest')) AND (`zone_id`=0)'

But when I removed outer braces I got valid QL:

SELECT * FROM `services` WHERE MATCH('@(description) smallest') AND `zone_id`=0;
Empty set (0.00 sec)

What is wrong with query which generated by Query class?

Server sent charset unknown to the client

Yii version 2.0.8
Yii Sphinx version latest-stable
Sphinx server version 2.0.4
PHP version 7.0.4
Operating system Ubuntu 14.04

Sphinx config

source ps_products
{
        type                    = mysql

        sql_host                = localhost
        sql_user                = root
        sql_pass                = 123
        sql_db                  = domobuv
        sql_port                = 3306  # optional, default is 3306

        #sql_query_pre          = SET NAMES utf_8
        sql_query               = \
                SELECT ps_products.id, ps_products.category_id, ps_products.brand_id, ps_products.material \
                FROM ps_products

        sql_attr_uint           = id
        sql_attr_uint           = category_id
        sql_attr_uint           = brand_id

        sql_query_info          = SELECT * FROM products WHERE id=$id
}


index ps_products_filters
{
        source                  = ps_products
        path                    = /var/lib/sphinxsearch/data/ps_products
        docinfo                 = extern
        charset_type            = utf-8
}


searchd
{
        listen                  = 9312
        listen                  = 9306:mysql41
        log                     = /var/log/sphinxsearch/searchd.log
        query_log               = /var/log/sphinxsearch/query.log
        read_timeout            = 5
        max_children            = 30
        pid_file                = /var/run/sphinxsearch/searchd.pid
        max_matches             = 1000
        seamless_rotate         = 1
        preopen_indexes         = 1
        unlink_old              = 1
        workers                 = threads # for RT to work
        binlog_path             = /var/lib/sphinxsearch/data
}

Yii config

'sphinx' => [
            'class' => 'yii\sphinx\Connection',
            'dsn' => 'mysql:host=127.0.0.1;port=9306;',
            'username' => '',
            'password' => '',
        ],

Query

$query = new \yii\sphinx\Query();
$rows = $query->from('ps_products_filters')->all();

Exception

Database Exception – yii\db\Exception
SQLSTATE[HY000] [2054] Server sent charset unknown to the client. Please, report to the developers

Trace part

1. in /var/www/vendor/yiisoft/yii2/db/Connection.php at line 547
538539540541542543544545546547548549550551552553554555556        $token = 'Opening DB connection: ' . $this->dsn;
        try {
            Yii::info($token, __METHOD__);
            Yii::beginProfile($token, __METHOD__);
            $this->pdo = $this->createPdoInstance();
            $this->initConnection();
            Yii::endProfile($token, __METHOD__);
        } catch (\PDOException $e) {
            Yii::endProfile($token, __METHOD__);
            throw new Exception($e->getMessage(), $e->errorInfo, (int) $e->getCode(), $e);
        }
    }

    /**
     * Closes the currently active DB connection.
     * It does nothing if the connection is already closed.
     */
    public function close()

How to change SetMatchMode?

I want change SetMatchMode to SPH_MATCH_ANY or SPH_MATCH_EXTENDED2.

Allow `ActiveDataProvider` set `Pagination::validatePage` automatically

If itemCount is determined using 'SHOW META' enabled Pagination::validatePage will break pagination.
It is better ActiveDataProvider disables Pagination::validatePage automatically.

sphinx distributed match error bug

select code:

result error:

mysql -h 192.168.110.40 -P 9306 shell connection select success;

sphinxql: outer select list must be a single star near

Camel-case facet name is not respected

Extracted from yiisoft/yii2#8579

Specifying facet as following:

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

Will produce PHP E_NOTICE: undefined index 'jsonAttr'.
This happens, because Sphinx returns column name in result set in lower case -> 'jsonattr'.

`Query::column()` does not respect `Query::indexBy()`.

Extracted from yiisoft/yii2#12649

Query::column() does not respect Query::indexBy().

Unit test to reproduce:

$result = (new Query)
            ->select('category_id')
            ->from('yii2_test_item_index')
            ->orderBy(['id' => SORT_DESC])
            ->indexBy('id')
            ->column($db);
        $this->assertEquals([2 => 2, 1 => 1], $result);

Sphinx if not working in select

Hello,

Sphinx IF function (http://sphinxsearch.com/docs/current.html#expr-func-if) does not work with Yii:

$query = new Query();
$query->select('IF(1=1,1,0) as x')
       ->from('myindex');
$query->createCommand()->queryAll();

Yii2 sphinx extension show less results then native query

What steps will reproduce the problem?

    $query = new Query();
    $query = $query->select(['id'])
        ->from('main')
        ->match('23|24');

With yii2 return only 2 rows! When I open console mysql client and run query it return 20 rows:
SELECT id FROM main WHERE MATCH('23|24');

What's expected?

I expect to see same results for both cases.

Additional info

| Yii vesion | 2.0.7
| PHP version | 7.0.5 x86_64
| Operating system | Linux 4.5.0-1
"yiisoft/yii2-sphinx": 2.0.5

Invalid UTF-8 sequence in argument error when calling json_encode() on sphinx record

There is a bug in Yii2 Sphinx Package which is causing UTF-8 encoding issues.

This issue reported in detail on;
http://stackoverflow.com/questions/31266012/sphinx-invalid-utf-8-sequence-in-argument-error-when-calling-json-encode-on

Integers should be handled the same way as floats

What steps will reproduce the problem?

->andFilterWhere('>=','year',$integer_var);

What's expected?

The query to work.

What do you get instead?

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected QUOTED_STRING, expecting CONST_INT or CONST_FLOAT or DOT_NUMBER or '-' near ''1400') AND (issue_year <= '1500')) AND (is_forsale=1)) ORDER BY title ASC LIMIT 25; SHOW META'

Additional info

Q	A
Yii version	2.0.10
Yii Sphinx version	lastest from composer
Sphinx server version	2.2.11
PHP version	7.0.12
Operating system	CentOS 7

Problem with column, which has data type bigint

Good day.
I have table with column id, which has type bigint. I create index for this table. When I try to filter data by id, I got error
SQLSTATE[42000]: Syntax error or access violation: 1064 index company_index: unsupported filter type 'string' on @id The SQL being executed was: SELECT * FROM company_index WHERE id='1'
Problem, of course, in extra quotes in WHERE clause, but manually I don't pass it. I find reason of the problem - I have 32-bit version of PHP, OS WIndows. In You extension I found code, which get DB-type of column and return its PHP-type. For column bigint You have condition - if PHP-version is 64-bit, return integer, else - return string (Class yii\sphinx\Schema, lines 431-432). And then, after got PHP-type, code generates SQL-query, which has filter with passed value and cast it to PHP-type (Class yii\sphinx\ColumnSchema, lines 98-108). In my system, this code generates string type for bigint column, value has been escaped with quotes and then I got SQL error. I think, it's a little bug, because in ColumnSchema You don't check type of column. How can I fix this problem and not change type of column to integer?

Exception when binding float params in findBySql

I am trying to use this code:

GeoSubwaySphinx::findBySql(
    'SELECT id, name, geodist(:lat, :lng , latitude, longitude,{in=degrees, out=m}) as distance
    FROM geo_subway_plain_main
    WHERE distance <= 2000 and id_geo_parent = :id_geo_parent ORDER BY distance ASC',[
        ':lat' => $lat,
        ':lng' => $lng,
        ':id_geo_parent' => $id_city,
    ]
)->all();

but there is an exception:

Exception (Database Exception) 'yii\db\Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 index geo_metro_plain_main: parse error: geodist() arguments can not be string
The SQL being executed was: SELECT id, name, geodist('55.788439', '49.119916' , latitude, longitude,{in=degrees, out=m}) as distance
FROM geo_subway_plain_main
WHERE distance <= 2000 and id_geo_parent = '2094' ORDER BY distance ASC'

in /var/www/beboss.ru/shamil/www/vendor/yiisoft/yii2-sphinx/Schema.php:527

is it a bug or a feature?

Sphinx DataProvider pagination over max_matches

Migrated from yiisoft/yii2#8492

If total count of possible results is greater then max_matches in sphinx options, there will be pagination issue that leads to exception.
This can be solved by setting somewhere max_matches property which should be used to calculate when "option max_matches" should be applied to the query. If pagesize*page > max_matches - add max_matches option.

Getting error General error: 2014 with facet()

Hi,

here is example of the code.

        $query = Item::find();
        $query->addFacets(['category_id']);
        $result = $query->search();

This code causes this error:

yii\db\Exception: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
The SQL being executed was: SHOW TABLES

If I remove facet() function call from code then search() works fine.

empty MVA field population error

What steps will reproduce the problem?

Populate ActiveRecord instance with empty MVA attribute with empty value. Check assigned value of this attribute.

What's expected?

Empty array ("[]")

What do you get instead?

Array with null element ("[NULL]")

Additional info

Q	A
Yii version	2.0.12
Yii Sphinx version	2.0.8
Sphinx server version	2.3.2
PHP version	7.1.3
Operating system	Debian Jessie

Error in QueryBuilder for sphinx

Method buildWithin have a mistake.
If set sorting by ASC, displayed error:

SQLSTATE [42000]: Syntax error or access violation: 1064 index INDEXNAME: no sort order defined

CONST_INT quoted like string in ->where(['<', 'attribute', $integer])

What steps will reproduce the problem?

            SpinSphinx::find()
                ->select('id')
                ->match($this->q)
                ->limit(2 * $this->pageSize)
                ->where(['<', 'song_time', time()])
                ->column();

What's expected?

No SQL error. The query should be

SELECT id FROM so_search_spin WHERE (MATCH('search term')) AND (song_time < 1487283583) LIMIT 200

What do you get instead?

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected QUOTED_STRING, expecting CONST_INT or CONST_FLOAT or DOT_NUMBER or '-' near ''1487283583') LIMIT 200'
The SQL being executed was: SELECT id FROM so_search_spin WHERE (MATCH('search term')) AND (song_time < '1487283583') LIMIT 200

Additional info

Q	A
Yii version	2.0.11.2
Yii Sphinx version	2.0.7
Sphinx server version	Server version: 2.2.11-id64-release (95ae9a6)
PHP version	PHP 7.0.12
Operating system	Ubuntu 14.04

How to insert data in table, not in index?

I have model Company

class Company extends \yii\sphinx\ActiveRecord
{
    public static function tableName()
    {
        return 'company';
    }

    public static function indexName()
    {
        return 'company_index';
    }
   ...
}

And I want to insert data in master-table, not in index. When I set fields with some values and call method save, I got error

index 'company_index' does not support INSERT

I have bad idea to create another model, which extends \yii\db\ActiveRecord class, and use it for insert, but I don't want to duplicate code in two models. Another idea - create method, for example, saveInTable and use it, but iin this way I need to clone and rename methods, such insert, insertInternal, update, updateInternal from \yii\db\ActiveRecord, which is not good idea, I think. Maybe anyone has better ideas or implement which task?

Sphinx defaultOrder bug?

This issue has originally been reported by @gonimar at https://github.com/yiisoft/yii2/issues/5655.
Moved here by @dynasource.

---

Undefined index: date

In /vendor/yiisoft/yii2/data/Sort.php(213) $this->attributes is empty.

use app\models\ArcCandidate;
use Yii;
use yii\rest\ActiveController;
use yii\data\ActiveDataProvider;
use yii\sphinx\Query;


class ArcCandidateController extends ActiveController
{
    public $modelClass = 'app\models\ArcCandidate';

    public function actionSearch($q)
    {
        $query = (new Query())->select([
            'id',
            'date',
        ])->from(ArcCandidate::indexName())->match($q);

        $provider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'defaultPageSize' => 10,
            ],
            'sort'=> [
                'defaultOrder' => [
                    'date' => SORT_DESC,
                ],
            ],
        ]);

        return $provider;
    }
}

<response>
<name>PHP Notice</name>
<message>Undefined index: date</message>
<code>8</code>
<type>yii\base\ErrorException</type>
<file>
...../vendor/yiisoft/yii2/data/Sort.php
</file>
<line>213</line>
<stack-trace>
<item>
#0 ...../vendor/yiisoft/yii2/data/Sort.php(213): yii\base\ErrorHandler->handleError(8, 'Undefined index...', '/var/www/vhosts...', 213, Array)
</item>
<item>
#1 ...../vendor/yiisoft/yii2/data/ActiveDataProvider.php(108): yii\data\Sort->getOrders()
</item>
<item>
#2 ...../vendor/yiisoft/yii2/data/BaseDataProvider.php(79): yii\data\ActiveDataProvider->prepareModels()
</item>
<item>
#3 ...../vendor/yiisoft/yii2/data/BaseDataProvider.php(92): yii\data\BaseDataProvider->prepare()
</item>
<item>
#4 ...../vendor/yiisoft/yii2/rest/Serializer.php(162): yii\data\BaseDataProvider->getModels()
</item>
<item>
#5 ...../vendor/yiisoft/yii2/rest/Serializer.php(131): yii\rest\Serializer->serializeDataProvider(Object(yii\data\ActiveDataProvider))
</item>
<item>
#6 ...../vendor/yiisoft/yii2/rest/Controller.php(97): yii\rest\Serializer->serialize(Object(yii\data\ActiveDataProvider))
</item>
<item>
#7 ...../vendor/yiisoft/yii2/rest/Controller.php(75): yii\rest\Controller->serializeData(Object(yii\data\ActiveDataProvider))
</item>
<item>
#8 ...../vendor/yiisoft/yii2/base/Controller.php(153): yii\rest\Controller->afterAction(Object(yii\base\InlineAction), Object(yii\data\ActiveDataProvider))
</item>
<item>
#9 ...../vendor/yiisoft/yii2/base/Module.php(455): yii\base\Controller->runAction('search', Array)
</item>
<item>
#10 ...../vendor/yiisoft/yii2/web/Application.php(83): yii\base\Module->runAction('arc-candidate/s...', Array)
</item>
<item>
#11 ...../vendor/yiisoft/yii2/base/Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request))
</item>
<item>
#12 ...../web/index.php(13): yii\base\Application->run()
</item>
<item>#13 {main}</item>
</stack-trace>
</response>

ActiveDataProvider page number issue

When using ActiveDataProvider without showMeta and if validatePage = true, getPage() returns zero instead of actual page number and pagination is not working.

        $query = $sphinxModel::find();
        $provider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 5,
                //'validatePage' => false,
            ],
       ]);

        var_dump($provider->getTotalCount());
        var_dump($_GET[$provider->getPagination()->pageParam]);
        var_dump($provider->getPagination()->getPage());

int 52336
string '5' (length=1)
int 0

QueryBuilder does not add params for Expression in where case

Mirror issue, as in yii2 yiisoft/yii2#9452

Yii2 snippets with match mode

when used at the same time match and snippetCallback, does not change the setting :qp2

in yii\sphinx\QueryBuilder

$params[$matchParamName] = $match; (411 string)

replaced by

if ($match instanceof Expression) {
$params[$matchParamName] = $match->params['match'];
} else {
$params[$matchParamName] = $match;
}

Create a function like BuildExcerpts

It would be great if the extension has a function like BuildExcerpts, you can find it https://code.google.com/p/sphinxsearch/source/browse/trunk/api/sphinxapi.php

findOne does not accept string as PK

Source: http://sphinxsearch.com/wiki/doku.php?id=fields_and_attributes

The first column is ALWAYS the document_id - and should be a unsigned 32 bit interger - it must be unique. (although sphinx can be compiled with 64 bit ID support). DONT define it as an attribute.

Error:

SQLSTATE[42000]: Syntax error or access violation: 1064 index rt: unsupported filter type 'string' on @id
The SQL being executed was: SELECT * FROM `rt` WHERE `id`='1810'

Error Info: Array
(
    [0] => 42000
    [1] => 1064
    [2] => index rt: unsupported filter type 'string' on @id
)

It's not mentioned in the documentation, but anyway, we know it's the primary key so we should not quote that param.

Problem with relation two sphinx models ( 1:N ): "syntax error, unexpected QUOTED_STRING, expecting CONST_INT"

First model:

class Child extends \yii\sphinx\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function indexName()
    {
        return '{{%child}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id'], 'required'],
            [['id'], 'unique'],
            [['id_parent'], 'integer'],
        ];
    }
}

Second model:

class Parent extends \yii\sphinx\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function indexName()
    {
        return '{{%parent}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id'], 'required'],
            [['id'], 'unique'],
            [['name'], 'string']
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getChilds()
    {
        return $this->hasMany(Child::className(), ['id_parent' => 'id']);
    }
}

Problem:

Parent::find()->with('childs')->all();

PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected QUOTED_STRING, expecting CONST_INT

andMatch does not ignore empty operands

What steps will reproduce the problem?

$query->andMatch(['title' => $this->title]);

What's expected?

The match to be ignored as andFilterWhere does in ActiveQuery.

What do you get instead?

Empty results.

Additional info

Q	A
Yii version	2.0.10
Yii Sphinx version	lastest from composer
Sphinx server version	2.2.11
PHP version	7.0.12
Operating system	CentOS 7

2.0.8 tag?

My composer.json for deployment has

    "yiisoft/yii2-sphinx": "dev-master",

because i need the timestamp type fix d989eff

I think dev-master is today no less stable than 2.0.7, so could you tag it?

Sphinx order by RAND()

Hi,

Still in the process of porting my current application from Yii to Yii 2 and come across (what I think) is a limitation with Sphinx.

In Yii 1 I could run the following SphinxQL: SELECT id FROM index_name ORDER BY RAND();

However, in Yii2 it doesn't seem to actually pay attention to the RAND() function.

I've tried two different approaches.
This one seems to ignore the RAND() and always returns the same row, if you do queryAll() it returns the same results each time.
$sql = "SELECT id FROM index_name ORDER BY RAND()";
$query = Yii::$app->sphinx->createCommand($sql)->queryOne();

Also, this way through an exception or can't find the column.
$query->select('id')
->from('index_name')
->orderBy('RAND()') // ->orderBy('RAND')
->limit(10);

Is there support for this? Or am I going at this wrong?

Thanks,
Gary

Setting multi value attributes (MVA) through createCommand()

Hey,

I'm trying to save an MVA attribute but can't seem to get it right, any ideas what's going wrong?

Yii::$app->sphinx->createCommand()->replace($this->indexTableName(), [
'id' => 1,
'test' => 'test',
'mva' => implode(',', [1, 2, 3]),
]);

I've tried various ways of setting the MVa just so I don't get an error, but none have worked.
'mva' => implode(',', [1, 2, 3]),
'mva' => 0,
'mva' => '',

Thanks
Gary

NULL attributes should be excluded

It seems like Sphinx doesn't like NULL values, nor does know anything about those parameters.

Source: http://sphinxsearch.com/forum/view.html?id=8052

The error I receive:

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected NULL, expecting CONST_INT (or 5 other tokens) near 'NULL)'
The SQL being executed was: REPLACE INTO `rt` (`id`, `user_id`, `first_name`, `last_name`, `phone`) VALUES (1798, '1798', 'Dasdasd', 'Fadas', NULL)

Error Info: Array
(
    [0] => 42000
    [1] => 1064
    [2] => sphinxql: syntax error, unexpected NULL, expecting CONST_INT (or 5 other tokens) near 'NULL)'
)

I would propose to automatically skip those attributes when setting them, like the indexer command line does with the disk-index.

Sphinx Query Error

Work

 $query->select(['id', 'title', 'price', 'product_category', 'product_properties'])
            ->from('products')
            ->limit(40)
            ->where('product_category=:product_category AND product_properties=:product_properties')
            ->params(
                [
                    ':product_category'=>5,
                    ':product_properties' => 5
                ]
            )
            ->all()

Dont't work

  $query->select(['id', 'title', 'price', 'product_category', 'product_properties'])
            ->from('products')
            ->limit(40)
            ->andWhere(
                [
                    'product_category'=>5,
                    'product_properties' => 5
                ]
            )
            ->all()

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected '(', expecting IDENT (or 5 other tokens) near '(product_category=5) AND (product_properties=5) LIMIT 40'
The SQL being executed was: SELECT id, title, price, product_category, product_properties FROM products WHERE (product_category=5) AND (product_properties=5) LIMIT 40

Brackets error

Where using function find()->match($match), there are automatical run escapeMatchValue for $match value. But when '(' is quoted - sphinx says error: index ******: syntax error, unexpected ')' near ')'.

Runtime -> Real-time

Why is Sphinx Real-time indexes named runtime indexes in this extension?

Maybe the naming should be changed to match Sphinx so that there would be no confusion? I had to double check that these are the same thing - kind of unneccessary step.

http://sphinxsearch.com/docs/current.html#rt-indexes

Distributed Index primary key attribute

For distributed index attribute primaryKey not assigned. In my application make this problem.

// yiisoft/yii2-sphinx/Schema.php
// Schema::findColumns()
    protected function findColumns($index)
    {
        $sql = 'DESCRIBE ' . $this->quoteSimpleIndexName($index->name);
        try {
            $columns = $this->db->createCommand($sql)->queryAll();
        } catch (\Exception $e) {
            $previous = $e->getPrevious();
            if ($previous instanceof \PDOException && strpos($previous->getMessage(), 'SQLSTATE[42S02') !== false) {
                // index does not exist
                // https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_bad_table_error
                return false;
            }
            throw $e;
        }

        if (empty($columns[0]['Agent'])) {
            foreach ($columns as $info) {
                $column = $this->loadColumnSchema($info);
                $index->columns[$column->name] = $column;
                if ($column->isPrimaryKey) {
                    $index->primaryKey = $column->name; // local index assign pk attribute
                }
            }
        } else {
            // Distributed index :
            $agent = $this->getIndexSchema($columns[0]['Agent']);
            $index->columns = $agent->columns;

            // FIX distributed index not assigned pk
            foreach ($index->columns as $name => $column) {
                if ($column->isPrimaryKey) {
                    $index->primaryKey = $column->name;
                }
            }
        }

        return true;
    }

->andWhere('field', []) 0=1 error

https://github.com/yiisoft/yii2-sphinx/blob/master/QueryBuilder.php#L837
SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected CONST_INT, expecting IDENT (or 56 other tokens) near '0=1)

$activeQuery->andWhere(['id' => []]);
$activeQuery->showMeta(false)->all();

Yii2-sphinx multiple server handling

Migrated from yiisoft/yii2#4849

@tibee :

since yii2-sphinx extends yii2 db handling capabilities, it does already support multiple master and multiple slave servers for load balancing and read-write separation.

However, since Sphinx does not support replication, cannot be configured that way as for example, MySQL replication. So, if I need to run multiple servers for load balancing, I have to set up multiple servers (as logical masters, without logical slaves), and I would need to write in every master.

My component config would be:

 'sphinx' => [
        'class' => 'yii\sphinx\Connection',
        'masters' => [
            ['dsn' => 'mysql:host=192.168.1.20;port=4300'],
            ['dsn' => 'mysql:host=192.168.1.21;port=4300'],
        ],
        'masterConfig' => [
            'username' => '',
            'password' => '',
        ],
    ],

The current implementation chooses only one master on a write operation, causing inconsistency.
A feature request would be the high-level support of multiple servers, meaning a loop would do the write operations on ALL masters.

Add/verify support for `QueryInterface::emulateExecution()`

Need to add support for recently added \yii\db\QueryInterface::emulateExecution() method, allowing to emulate execution in yii\sphinx\Query methods.

See yiisoft/yii2#12708

Limit not honored by Query object

What steps will reproduce the problem?

Do a query wich returns more than 1000 rows.

What's expected?

The exact number of matched rows as result.

What do you get instead?

Only the first 1000 rows

Additional info

The ActiveDataProvider object already by-pass this problem. I solved this by myself by adding this line :
$query->options['max_matches'] = $offset + $limit;
This should also be present directly in the Query object to solve the problem.

Q	A
Yii version	2.0.6
Yii Sphinx version	2.0.5
Sphinx server version	2.2.9-id64-release
PHP version	7.0.9
Operating system	Debian 3.16.7

GROUP 3 BY

Hello,
Is it possible to group the results as described in the documentation?

Starting with 2.2.1-beta, you can query Sphinx to return (no more than) N top matches for each group accordingly to WITHIN GROUP ORDER BY.

SELECT id FROM products GROUP 3 BY category

Thanks

yii2-sphinx persistent connection

I have a problem with "too many connections". I am trying to use pdo persistent connection attribute, but error is still raising sometimes.
yii2-config:

'sphinx' => [
            'class' => 'yii\sphinx\Connection',
            'dsn' => 'mysql:host=10.0.0.1;port=9306;',
            'username' => '',
            'password' => '',
            'enableSchemaCache' => true,
            'schemaCacheDuration' => 3600,
            'attributes' => [
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_TIMEOUT => 30,
            ]
        ],

sphinx-config:

searchd
{
        listen = 9312
        listen = 9306:mysql41
        log = /var/log/sphinx/searchd.log
        query_log = /var/log/sphinx/query.log
        read_timeout = 5
        client_timeout = 30
        max_children = 100
        pid_file = /var/log/sphinx/searchd.pid
        seamless_rotate = 1
        preopen_indexes = 1
        unlink_old = 1
        workers = threads
        dist_threads = 3
        binlog_path = /var/lib/sphinx
        mysql_version_string = 5.0.37
}

Please help me, whats wrong?

SHOW META via SphinxQL

Hello. How i can fetch meta data using raw sphinxQL (not QueryBuilder) in one query?

$sql = "SELECT * FROM index; SHOW META"
Yii::$app->sphinx->createCommand($sql)->fetchAll();

This returns data for first part of the query (SELECT * FROM index);

Setting Geo Filters

I am using Yii2 Sphinx but there is no option for setting GEO filters such as latitude and longitude.
For a simple query , we can use ->select() but right now i am using join query and query is written in sphinx.conf file. Now i am stuck that how should i pass the lat and long to the query in conf file as we can change filters using ->andwhere().

Doc mistake

https://github.com/yiisoft/yii2-sphinx/blob/master/docs/guide/usage-match.md

->match(new Expression(':match', ['match' => '@(content) ' . Yii::$app->sphinx->escapeMatchValue($_POST['search'])]))

Replace 'match' on ':match'.

MVA attributes

Hi!
A short time ago we have update yii2 framework to last release, sphinx now is says error for mva attribute.
Query:
$countVideos = Video::find()
->where(['kladrs' => [0, 110828]])
->count();
Error:
SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax error, unexpected QUOTED_STRING, expecting CONST_INT or '-' near ''0', '110828')'
The SQL being executed was: SELECT COUNT(*) FROM video WHERE kladrs IN ('0', '110828')

Why he quotes int like strings? How to repair this:(( Any ideas?