How to Add OR Condition in Laravel Sphinx query?

Question

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

It will depend on the version of sphinx being used. OR in where is a modern construct, older versions didn't support. Sounds like you have older searchd version — barryhunter, Jul 17 '22 at 07:26

score 0 · Answer 1 · answered Jul 12 '22 at 12:43

0

you cant use OR condition in Sphinx
you can handle it with whereNotIn and whereIn

answered Jul 12 '22 at 12:43

Mehran

282
1
6
17

but how ? can you give example of $query->where('state_id', '!=', 0)->orWhere('city_id', '!=', 0); into whereNotIn and whereIn converting – Sejal Gauswami Jul 12 '22 at 13:48
something like this $query->whereNotIn ('state_id', 0); $query->whereNotIn ('city_id', 0); @SejalGauswami – Mehran Jul 12 '22 at 20:48
but this will return true only if both state_id field and city_id field both has value. i need result true if any of the field either state_id or city_id has value then true – Sejal Gauswami Jul 13 '22 at 07:29

score 0 · Answer 2 · answered Jul 13 '22 at 07:04

0

These are the top rated real world PHP examples of yii\sphinx\Query::orWhere extracted from open source projects. You can rate examples to help us improve the quality of examples. Please Visit and see example

answered Jul 13 '22 at 07:04

Waqas Altaf

392
3
16

But in laravel how can i implement ? i tried $query->where('state_id', '!=', 0)->orWhere('city_id', '!=', 0); but its not working it gives error – Sejal Gauswami Jul 13 '22 at 07:32
What error it gives. share error message here for more clarity. – Waqas Altaf Jul 13 '22 at 09:05
"Syntax error or access violation: 1064 sphinxql: syntax error, unexpected OR, expecting $end near 'or " – Sejal Gauswami Jul 13 '22 at 09:40

score 0 · Accepted Answer · answered Jul 19 '22 at 09:14

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

How to Add OR Condition in Laravel Sphinx query?

3 Answers3