CODEWITHSUNDEEP
laravellaravel-4eloquent
sndrsnk
sndrsnk

Reputation: 498

Nested 'AND OR' Query in Eloquent

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.

Upvotes: 41

Views: 45217

Answers (3)

Opaida
Opaida

Reputation: 111

You can use where then additional where and pass conditions as arrays. Between two where is 'And' operation. Inside the second where is 'Or' operation.

Example:

$users = DB::table('users')->where('status', 'value')->where([
    ['age', '>', '10'],
    ['subscribed', '<>', '1'],
])->get();

Upvotes: 1

Gabi Dj
Gabi Dj

Reputation: 617

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();

Upvotes: 2

Razor
Razor

Reputation: 9835

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

Upvotes: 73

Related Questions