Laravel Eloquent multiple wheres gives wrong results-CodePudding

Why this code below is returning wrong results?

        $player->wins = Game::where('home_player_1', '=', $player->id)
                            ->orWhere('home_player_2', '=', $player->id)
                            ->orWhere('home_player_3', '=', $player->id)
                            ->orWhere('home_player_4', '=', $player->id)
                            ->where('result', '=','1') // 1 = win
                            ->get();

        $player->losses = Game::where('home_player_1', '=', $player->id)
                            ->orWhere('home_player_2', '=', $player->id)
                            ->orWhere('home_player_3', '=', $player->id)
                            ->orWhere('home_player_4', '=', $player->id)
                            ->where('result', '=','0') // 0 = lose
                            ->get();

        $player->matches = Game::where('home_player_1', '=', $player->id)
                            ->orWhere('home_player_2', '=', $player->id)
                            ->orWhere('home_player_3', '=', $player->id)
                            ->orWhere('home_player_4', '=', $player->id)
                            ->get();

What i get is like 5matches / 5 wins / 5losses when in real its 5/5/0. How should i do it?

CodePudding user response：

Move your home_player_x clauses into a Subquery:

$player->wins = Game::where(function ($query) use ($player) {
  return $query->where('home_player_1', $player->id)
  ->orWhere('home_player_2', $player->id)
  ->orWhere('home_player_3', $player->id)
  ->orWhere('home_player_4', $player->id);
})->where('result', 1)
->get();

...

This way, it doesn't conflict with that last ->where('result') clause.

Additionally, since you're reusing this clause multiple times, define it as a variable, and reuse it:

$conditional = function ($query) use ($player) {
  return $query->where(function ($subQuery) use ($player) {
    return $subQuery->orWhere('home_player_1', $player->id)
    ->orWhere('home_player_2', $player->id)
    ->orWhere('home_player_3', $player->id)
    ->orWhere('home_player_4', $player->id);
  })
});

$player->wins = Game::where($conditional)->where('result', 1)->get();
$player->losses = Game::where($conditional)->where('result', 0)->get();
$player->matches = Game::where($conditional)->get();

Or, consider defining this as a Scope on the Game model, so you can call Game::playerMatches($player)->where('result', 1)->get(), etc. See the Documentation for that:

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

I won't really touch too much on this, but defining numbered columns, like home_player_1, home_player_2, etc. is generally considered bad data practice. You should define a home_players table, or games_players, with a type column for home or away table and associate Game and Players that way.

CodePudding user response：

Check out the post on Logical Grouping in the Laravel Docs. I think based on that, your statement for matches should be:

$player->matches = Game::where(function ($query) use ($player) {
    $query->where('home_player_1', '=', $player->id)
          ->orWhere('home_player_2', '=', $player->id)
          ->orWhere('home_player_3', '=', $player->id)
          ->orWhere('home_player_4', '=', $player->id);
})->get();

I am not sure if this helps as I do not have the rest of the code, but please test it and let me know.

Also from the docs: "In fact, you should generally always group calls to the orWhere method in parentheses in order to avoid unexpected query behavior."

CodePudding user response：

Need to modify the orWhere() condition, Instead of above you can use below where clause:

$player->wins = Game::where('result', '=','1') // 1 = win
    ->where(function ($query) use ($player) {
        $query->where('home_player_1', '=', $player->id)
            ->orWhere('home_player_2', '=', $player->id)
            ->orWhere('home_player_3', '=', $player->id)
            ->orWhere('home_player_4', '=', $player->id);
    })->get();

$player->losses = Game::where('result', '=', '0') // 0 = lose
    ->where(function ($query) use ($player) {
        $query->where('home_player_1', '=', $player->id)
            ->orWhere('home_player_2', '=', $player->id)
            ->orWhere('home_player_3', '=', $player->id)
            ->orWhere('home_player_4', '=', $player->id);
    })->get();


$player->matches = Game::
    where(function ($query) use ($player) {
        $query->where('home_player_1', '=', $player->id)
            ->orWhere('home_player_2', '=', $player->id)
            ->orWhere('home_player_3', '=', $player->id)
            ->orWhere('home_player_4', '=', $player->id);
    })->get();

When there were multiple orWhere() then normal conditions do not work, Need to add them under a single where.