CODEWITHSUNDEEP
mysqllaravelsearch
azbroja
azbroja

Reputation: 95

Issue with multiple search in Laravel and MySql

I have problem with implementation multiple search in Method in laravel, I have code like below 

$search_fields = ['name', 'symbol'];

$searchValues = preg_split('/\s+/', $request->q, -1, PREG_SPLIT_NO_EMPTY);

$query = function ($q) use ($searchValues, $search_fields) {
    foreach ($searchValues as $value) {
        foreach ($search_fields as $field){
      $q->where($field, 'like', "%{$value}%")
      ->orWhere($field, 'like', "%{$value}%");}
      ;}};


return $products = Product::where($query)->orderBy('name', 'asc')->take(100)->get();

but it isn't working becouse generate sql like this:

select
*
from
  `products`
where
(
  `name` like "%261%"
  or `name` like "%261%"
  and `symbol` like "%261%"
  or `symbol` like "%261%"
)
order by
  `name` asc
limit
100

I need like below but I don't know how to do it in Laravel :(

select
*
from
 `products`
where
(
  `name` like "%hp%"
  or `symbol` like "%hp%")
  and (`name` like "%261%"
  or `symbol` like "%261%"
)
order by
  `name` asc
limit
100

Upvotes: 0

Views: 67

Answers (1)

Jerodev
Jerodev

Reputation: 33186

You need to group the wheres that are between parenthesis in closures. So your $query function should be updated like this:

$query = function ($q) use ($searchValues, $search_fields) {
    foreach ($searchValues as $value) {
        foreach ($search_fields as $field) {
            $q->where(function ($query) use ($field, $value) {
                $query
                    ->orWhere($field, 'like', "%{$value}%")
                    ->orWhere($field, 'like', "%{$value}%");    
            });
        }
    }
};

This lets the query builder know what where's to put together.

Upvotes: 1

Related Questions