[SOLVED] How to Add OR Condition in Laravel Sphinx query?

How to Add OR Condition in Laravel Sphinx query?

I am using the fobia/laravel-sphinx package and want to add a condition like city != 0 or state != 0 but the or condition is not working and gives tjis error:

"Syntax error or access violation: 1064 sphinxql: syntax error, unexpected OR, expecting $end near 'or "

$query->where('state_id', '!=', 0)->orWhere('city_id', '!=', 0);

It uses \Fobia\Database\SphinxConnection\Eloquent\Model

Solution

If you have a version of sphinx/manticore server that is not supporting OR conditions, have to 'fake' using a fake attribute/expression.

Need to add a column. Want SphinxQL somewhat like

SELECT *, (state_id!=0)+(city_id!=0) as filter FROM index WHERE filter > 0

... ie will only inlcude rows, where the sum of state_id and city_id is above zero. Ie either one of them is not zero.

Alas reading the laravel-sphinx source, can't figure out how to add to the columns list. Seems needs needs adding to $query->columns. So dont know if something like

 $query->columns[] = "(state_id!=0)+(city_id!=0) AS filter";
 $query->where('filter', '>', 0);

or maybe

 $query->select(['*',"(state_id!=0)+(city_id!=0) AS filter"])->where('filter', '>', 0);

seems the there is a 'select' method in the base query builder https://laravel.com/api/5.5/Illuminate/Database/Query/Builder.html