Eloquent 中的嵌套“AND OR"查询 [英] 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

然而，这总是返回表中的第一个值(其中 open 是 1 或 0)，这是不正确的.为了使查询正确，它需要在括号中包含两组 AND 查询，如下所示:

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

可以在 Laravel 中做到这一点吗?我试图这样做；然而，它失败了:

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;
}

非常感谢任何帮助.

推荐答案

你离答案很近了

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

这里是两个代码的区别

这篇关于Eloquent 中的嵌套“AND OR"查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！