How to group where clauses in Laravel Query Builder correctly

Question

I am running the following query using the search() function below - the problem is I need to group the where clauses - what am I doing wrong?

select `standings`.*, `users`.`name` as `user` from `standings` 
left join `users` on `standings`.`user_id` = `users`.`id` 
where `users`.`name` like '%bob%' or `users`.`email` like '%bob%' 
and `standings`.`tenant_id` = '1'

In my Standings model I have the following search() that performs the WHERE clause

public static function search($query)
{
  return empty($query) ? static::query()
         : static::where('users.name', 'like', '%'.$query.'%')
         ->orWhere('users.email', 'like', '%'.$query.'%');
}

public function render()
{
    $query = Standing::search($this->search)
        ->select('standings.*', 'users.name AS user')
        ->leftJoin('users', 'standings.user_id', '=', 'users.id')
        ->orderBy('points', 'desc')
        ->orderBy('goals_difference', 'desc')
        ->orderBy('goals_for', 'desc');
    if($this->super && $this->selectedTenant) {
        $query->where('standings.tenant_id', $this->selectedTenant);
    }

    return view('livewire.show-standings', [
        'standings' => $query->paginate($this->perPage)
    ]);

}

The query works however it doesn't group the WHERE clause correctly on the users.name & users.email fields - how do I change this search() function so the WHERE query has them grouped like this

where (`users`.`name` like '%bob%' or `users`.`email` like '%bob%')`

Answer 1

You need to group the where clauses in a wrapping where clause. Try this

public static function search($query)
{
  return empty($query) 
    ? static::query()
    : static::where(function($query){
        $query->where('users.name', 'like', '%'.$query.'%')
            ->orWhere('users.email', 'like', '%'.$query.'%');
      });
}

Thanks that for some reason even though looks correct gives me the following error - Object of class Illuminate\Database\Eloquent\Builder could not be converted to string NB I am using Laravel with Livewire (not sure if that should make any difference)

$query->where('users.name', 'like', '%'.$query.'%') and ->orWhere('users.email', 'like', '%'.$query.'%'); is giving the error because while trying to compare $query is being treated as a string hence the error

You can define the search as a query scope on the model

//Assuming a relation Standing belongsTo User
//Query constraint to get all Standing records where
//related User record's name or email are like searchTerm
public function scopeSearch($query, string $searchTerm)
{
    return $query->whereHas('user', function($query) use($searchTerm){
        $query->where('name', 'like', "%{$searchTerm)%")
            ->orWhere('email', 'like', "%{$searchTerm}%");
    });
}

Laravel docs:https://laravel.com/docs/8.x/eloquent#local-scopes

With the above search scope defined on Standing model, you can have the render function as

public function render()
{
    $query = Standing::with('user:id,name')
        ->search($this->search)
        ->orderBy('points', 'desc')
        ->orderBy('goals_difference', 'desc')
        ->orderBy('goals_for', 'desc');
    if($this->super && $this->selectedTenant) {
        $query->where('tenant_id', $this->selectedTenant);
    }

    return view('livewire.show-standings', [
        'standings' => $query->paginate($this->perPage)
    ]);

}