Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

johnrudolphdrexler's avatar

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!

0 likes
4 replies
MohamedTammam's avatar

Do you also want to return records where the games.winner equals to players.id? Which means the second row in your example?

johnrudolphdrexler's avatar

I'm hoping to only return records where games.winner = players.id. So the second row would be returned, but the first would not.

Please or to participate in this conversation.