Do you also want to return records where the games.winner equals to players.id?
Which means the second row in your example?
Help with an eloquent query that is using a join
I made this eloquent query:
$games = DB::table('games')
->join('players', 'games.id', '=', 'players.game_id')
->join('users', 'players.user_id', '=', 'users.id')
->select(
'games.id as game_id',
'users.id as user_id',
'games.winner as winner',
'players.id as player_id',
'games.created_at',
)
->where('user_id', $this->user->id)
->get();
And it gives me the exact results I expect:
[{"game_id":7,"user_id":3,"winner":7,"player_id":8,"created_at":"2022-04-24 00:17:09"},{"game_id":8,"user_id":3,"winner":10,"player_id":10,"created_at":"2022-04-24 01:27:17"}]
As you can see, "winner" != "player_id" in the first record, but they are equal in the second record (meaning the player lost the first game, but won the second). However, if I attempt to add "->where('winner', 'player_id')" to the query, it returns nothing. The query all together looks like this:
$wins = DB::table('games')
->join('players', 'games.id', '=', 'players.game_id')
->join('users', 'players.user_id', '=', 'users.id')
->select(
'games.id as game_id',
'users.id as user_id',
'games.winner as winner',
'players.id as player_id',
'games.created_at',
)
->where('user_id', $this->user->id)
->where('winner', 'player_id')
->get();
Instead of "->where('winner', 'player_id')" I have also tried "->where('games.winner', 'players.id')." Any ideas what I'm doing wrong here? Thanks!
->where('winner', 'player_id')
Because the query is finding the column winner where the value is 'player_id`.
To compare the value with the column, you should use whereColumn
https://laravel.com/docs/9.x/queries#additional-where-clauses
Please or to participate in this conversation.