I am trying to implement filter search result through ajax in laravel 5.4 Currently i am facing a problem with whereBetween when combined with other where statement.
I have an query statement as below:
$query = $this->hotel->query();
$query = $query->where(function ($query){
$query->orWhereBetween('min_price' , [500 , 1000]);
$query->orWhereBetween('min_price' , [5000 , 50000]);
});
$query = $query->whereIn('internet', [1,2]);
$query = $query->whereIn('breakfast', [1,2]);
$query = $query->where('language', 'LIKE', '%'.'english'.'%');
$hotel = $query->get();
foreach($hotel as $h) {
echo $h->id.'<br>';
} exit();
The above query doesn't list the result of price between 500 to 100 and 5000 to 50000 There are two rows matching the conditions above but it return only single row.
Database data
id min_price internet breakfast language
4 876 2 2 english
5 40000 1 2 english
Result: The above query result only id 4, but should return both ids 4 and 5.
I think I am missing something on line
$query = $query->where(function ($query){
$query->orWhereBetween('min_price' , [500 , 1000]);
$query->orWhereBetween('min_price' , [5000 , 50000]);
});
How can I achieve correct result? Please help.
Not sure with your requirements but you may need to arrange your where conditions like
$query = $query->orWhere(function ($q){ //assuming b/w is not mandatory
$q->whereBetween('min_price' , [500 , 1000]); // removed OR from here
$q->orWhereBetween('min_price' , [5000 , 50000]);
});
Change your inner where clause variable name.
$query = $query->where(function ($q){
$q->WhereBetween('min_price' , [500 , 1000]);
$q->orWhereBetween('min_price' , [5000 , 50000]);
});