Nested 'AND OR' Query in Eloquent

Question

I'm currently attempting to create a nested query, as follows:

public function getChallenge($user_id, $opponent_id) {     $challenge = $this->challenges()             ->where('open', true)             ->where(function($query) use ($user_id, $opponent_id) {                     $query->where('player_1', $user_id)                           ->where('player_2', $opponent_id);                 })                  ->orWhere(function($query) use ($opponent_id, $user_id) {                     $query->where('player_1', $opponent_id)                           ->where('player_2', $user_id);                 })             ->first();      return $challenge; } 

This creates the following query for example:

select * from `site_challenges_leagues`  where `site_challenges_leagues`.`league_id` = '1'  and `open` = '1' and (`player_1` = '3' and `player_2` = '1')  or (`player_1` = '1' and `player_2` = '3')  limit 1 

However, this always returns the first value in the table (where open is either 1 or 0), which is incorrect. For the query to be correct, it needs to contain both sets of AND queries in brackets, as follows:

 select * from `site_challenges_leagues`   where `site_challenges_leagues`.`league_id` = '1'   and `open` = TRUE    and ((`player_1` = '3' and `player_2` = '1')   or (`player_1` = '1' and `player_2` = '3'))   limit 1 

Is it possible to do this in Laravel? I attempted to do this; however, it failed:

public function getChallenge($user_id, $opponent_id) {     $challenge = $this->challenges()             ->where('open', true)             ->where(function($q) use ($user_id, $opponent_id) {                 $q->where(function($query) {                         $query->where('player_1', $user_id)                               ->where('player_2', $opponent_id);                     })                   ->orWhere(function($query) {                         $query->where('player_1', $opponent_id)                               ->where('player_2', $user_id);                     })                 })             ->first();      return $challenge; } 

Any help is greatly appreciated.

Answer 1

You were very close to the answer

$challenge = $this->challenges()         ->where('open', true)         ->where(function($q) use ($user_id, $opponent_id) {             $q->where(function($query) use ($opponent_id, $user_id){                     $query->where('player_1', $user_id)                           ->where('player_2', $opponent_id);                 })               ->orWhere(function($query) use ($opponent_id, $user_id) {                     $query->where('player_1', $opponent_id)                           ->where('player_2', $user_id);                 });             })         ->first(); 

Here are the differences between two codes

Answer 2

I needed the nested wheres to search for a user by multiple fields:

    $users_query->where('company_uuid', '=', $company_uuid);     $users_query->where('is_active', '=', true);     $users_query->whereNested(function($query) use ($search){         /** @var Builder $query */         $query             ->where('first_name', 'like', '%' . $search . '%')             ->orWhere('last_name', 'like', '%' . $search . '%')             ->orWhere('email', 'like', '%' . $search . '%');     }); 

Supposing you want to stack where layers without losing them on using orWhere Wouldn't this be the same thing ?

   $challenge = $this->challenges()        ->where('open', true)        ->whereNested(function($q) use ($user_id, $opponent_id) {            $query->where('player_1', $user_id)                ->where('player_2', $opponent_id);             $query->orWhere('player_1', $opponent_id)                ->where('player_2', $user_id);    })->first();