Question 15472 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel search using multiple condition (orWhere / orWhereHas)

I am creating a search field where user can search any data from any field from the list of displayed records. I have set the condition where the displayed records should only be the ones with the percentage of 50. The initial displayed records work well, where it only shows a list of records with the percentage of 50.

However, when I tried searching for records with specific word, it will display all records that contain the searched word regardless of its percentage. For example, if I search for a place_name called "hotel" it will list down all the records with place_name that contain the word "hotel" even if the percentage is not 50.

The only filter that works is the place_id where it doesn’t display other records that do not have a percentage of 50. For example if I search for the place_id that contains "XXX", the list will display the records that contain "XXX" in its word AND has the percentage of 50.

Below is my code. I assumed this happens because of orWhere / orWhereHas. I’m not sure if I use it correctly. Any advice?

$assetSurvey = new AssetSurvey();
$workInProgressSurvey = $assetSurvey->where('percentage', '50');

$survey = $workInProgressSurvey->when($search, function ($query, $search) {
    $query->where('place_id', 'like', '%'.$search.'%')
        ->orWhere('place_name', 'like', '%'.$search.'%')
        ->orWhere('address', 'like', '%'.$search.'%')
        ->orWhereHas('client', function ($query) use ($search) {
            $query->where('description', 'like', '%'.$search.'%');
        })->orWhereHas('state', function ($query) use ($search) {
            $query->where('description', 'like', '%'.$search.'%');
        })->orWhereHas('organization', function ($query) use ($search) {
            $query->where('name', 'like', '%'.$search.'%');
        });
    })->offset(($page - 1) * $paginate)
    ->paginate($paginate);

return response()->json($survey);
}
Tags:

2

Answers


  1. 0

    If i understood your goal, you have to add a ‘where’ to group and put in ‘AND’ all the conditions in ‘OR’

    $survey = $workInProgressSurvey->when($search, function ($query, $search) {
    
    $query->where(function ($_query) use ($search) {     // ------ wrap with this
        
        $_query->where('place_id', 'like', '%'.$search.'%')
        
               ->orWhere('place_name', 'like', '%'.$search.'%')
              
               ->orWhere('address', 'like', '%'.$search.'%')
              
               ->orWhereHas('client', function ($query) use ($search) {
                  
                    $query->where('description', 'like', '%'.$search.'%');
                 })
                
              ->orWhereHas('state', function ($query) use ($search) {
                  
                    $query->where('description', 'like', '%'.$search.'%');
                
                })
                
              ->orWhereHas('organization', function ($query) use ($search) {
                    
                    $query->where('name', 'like', '%'.$search.'%');
                
                });
        });             // ------ end wrap
    })
    
    ->offset(($page - 1) * $paginate)
    
    ->paginate($paginate);
    Login or Signup to reply.
  2. 0

    Because you use orWhere in when method, the query will get any records that match one of your conditions. Try this:

    $assetSurvey = new AssetSurvey();
$workInProgressSurvey = $assetSurvey->where('percentage', '50');

$survey = $workInProgressSurvey->when($search, function ($query, $search) {
    $query->where(function ($query) use ($search) {
        $query->where('place_id', 'like', '%' . $search . '%')
            ->orWhere('place_name', 'like', '%' . $search . '%')
            ->orWhere('address', 'like', '%' . $search . '%')
            ->orWhereHas('client', function ($query) use ($search) {
                $query->where('description', 'like', '%' . $search . '%');
            })->orWhereHas('state', function ($query) use ($search) {
                $query->where('description', 'like', '%' . $search . '%');
            })->orWhereHas('organization', function ($query) use ($search) {
                $query->where('name', 'like', '%' . $search . '%');
            });
    });
})->offset(($page - 1) * $paginate)
    ->paginate($paginate);

return response()->json($survey);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search