如何在Laravel查询构建器中根据条件获取对手玩家ID

我在MySQL中有对应的表结构，已将玩家ID存储在变量$player_id中，该ID对应表中的player_1或player_2字段值。需要获取对手玩家ID：若player_1等于$player_id则取player_2，反之取player_1。此前用foreach处理效果不佳，希望在Laravel查询构建器中通过条件判断实现，现有代码如下：

public function RedirectToPersonal()
{
    $torneo = Cache::get('torneo');
    //return $torneo;
    $player_id = DB::table('players')
        ->where('FirstName', '=', Auth::user()->firstName)
        ->where('LastName', '=', Auth::user()->lastName)
        ->value('player_id');

    $player_data = DB::table('tabella_' . $torneo)
        ->orderByDesc('CurrentRoundNumber')
        ->where('player_1', '=', $player_id)
        ->orWhere('player_2', '=', $player_id)
        ->get()
        ->toArray();
    //dump($player_data);

    foreach ($player_data as $key => $value) {
        if ($player_data[$key]->player_1 = $player_id) {
            $names[] = $player_data[$key]->player_2;
        } else {
            $names[] = $player_data[$key]->player_1;
        }
    }
    //dump($player_id);
    //dump($names);

    $data = [
        "player_data" => $player_data,
    ];
    return view('user.personal', $data);
}

可以直接在查询构建器中通过SQL条件逻辑获取对手ID，把计算逻辑交给数据库处理，无需额外循环，同时还能避免原代码中的逻辑错误：

推荐方案：使用CASE语句在查询中直接生成对手ID #

public function RedirectToPersonal()
{
    $torneo = Cache::get('torneo');
    
    $player_id = DB::table('players')
        ->where('FirstName', Auth::user()->firstName)
        ->where('LastName', Auth::user()->lastName)
        ->value('player_id');

    // 修改查询逻辑，直接返回包含对手ID的结果集
    $player_data = DB::table('tabella_' . $torneo)
        // 新增opponent_id字段，通过CASE逻辑判断获取对手ID
        ->select('*', DB::raw("CASE WHEN player_1 = ? THEN player_2 ELSE player_1 END AS opponent_id", [$player_id]))
        ->orderByDesc('CurrentRoundNumber')
        // 用闭包包裹orWhere，避免后续添加其他条件时出现逻辑冲突
        ->where(function ($query) use ($player_id) {
            $query->where('player_1', $player_id)
                  ->orWhere('player_2', $player_id);
        })
        ->get();

    // 结果集中每个对象都会自带opponent_id字段，直接在视图中使用即可
    $data = [
        "player_data" => $player_data,
    ];
    return view('user.personal', $data);
}

补充说明原代码问题 #

原foreach中使用了赋值运算符=而非比较运算符==，这是导致循环效果不佳的直接原因，但通过查询构建器处理的方式更高效且更易维护。

内容的提问来源于stack exchange，提问作者user21357629