Yii2 Sphinx Left join not working

Question

I have been trying to use Yii-Sphinx extension and its working fine when i used a simple query but when i try to use left join then it does not work. It returns the below error. I have tested many queries but not working. I am using Yii-Sphinx extension

SQLSTATE[42000]: Syntax error or access violation: 1064 sphinxql: syntax  
error, unexpected IDENT, expecting $end near 'LEFT JOIN specs ON specs.id =   
listing.specs_id'
The SQL being executed was: SELECT specs.id, listing.title,listing.specs_id,    
listing.reg_no, listing.price, listing.status, listing.featured FROM listing   
LEFT JOIN specs ON specs.id = listing.specs_id
Error Info: Array
(
    [0] => 42000
    [1] => 1064
    [2] => sphinxql: syntax error, unexpected IDENT, expecting $end near   
    'LEFT JOIN specs ON specs.id = listing.specs_id'
 )

here is my query

SELECT specs.id, listing.title,listing.specs_id, listing.reg_no,  listing.price, listing.status, listing.featured FROM listing LEFT JOIN specs ON specs.id = listing.specs_id

Answer 1

I have solved this issue. Here is the detail for anybody who gets stuck in such an issue. Following is my solution with sphinx query builder available in yii2-sphinx extension:

$q = new Query();
$q->from('listing');
$rows = $q->all();

'listing' is the index from the sphinx config file, Join query can be written in sql_query

What i was doing wrong that i was using the simple query rather than query builder and that solved the issue. For example, join query will not work with the below code.

 $sql = 'Select * FROM listing';
 $rows = Yii::$app->sphinx->createCommand($sql)->queryAll(); 

Simple query will be executed but the query with any join will return error.

Answer 2

SphinxSearch itself does not support 'JOIN's. It can't run such queries.

Not a problem with yii2-sphinx as such, its how Sphinx Works.

If writing SphinxQL queries directly, can read the SELECT syntax here: http://sphinxsearch.com/docs/current.html#sphinxql-select

SELECT statement was introduced in version 0.9.9-rc2. It's syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs).

In general use QueryBuilder http://www.yiiframework.com/doc-2.0/yii-sphinx-querybuilder.html as it only offers 'methods' actully supported by Sphinx.