Laravel Eloquent relationship - multiple columns of table reference same foreign key

Question

Laravel/Eloquent newbie here. I am implementing a simple board game. Each game has 4 players. The tables structure consists of a Players table and a Games table:

SELECT * FROM players;

id    | name        |
---------------------
1     | John        |
2     | Mary        |
3     | Linda       |
4     | Alex        |
5     | Chris       |
6     | Ron         |
7     | Dave        |

SELECT * FROM games;

id    | player1_id  | player2_id  | player3_id    player4_id  
---------------------------------------------------------------------
1     | 1           | 2           | 3           | 4
2     | 3           | 5           | 6           | 7
3     | 2           | 3           | 5           | 6
4     | 2           | 4           | 5           | 7

Goal: I want to be able to get all games a player has participated in.

For this I am trying to write a function games() in the Player model. For player with id 2 this should return games 1, 3, 4 / for player with id 3 it should return games 1, 2, 3 and so forth.

With raw SQL I would do something like this:

SELECT * FROM games WHERE 
  (player1_id = 2 OR player2_id = 2 OR player3_id = 2 OR player4_id = 2)

But with Eloquent I'm having a hard time figuring out how one must set up this relationship to achieve this.

Equivalently I'd also like to be able to do the opposite - to return all players of a game - with a function players() in the Game model.

The models:

// Models/Player.php

//...

class Player extends Model
{
    public function games(){

        //?

    }
}

// Models/Game.php

//...

class Game extends Model
{
    public function players(){

        //?

    }
}

`DB::table('games')->where('player1_id', '=', `id`)->orwhere('player2_id', '=', 'id')->orwhere('player3_id', '=', 'id')->orwhere('player4_id', '=', 'id')->get();` — Hassaan Ali, Apr 09 '21 at 21:22
@HassaanAli No !!!!!! Use relationships, stop with `DB::something`, think that `DB::something` does not exist for simple operations !!!!!!!!! — matiaslauriti, Apr 09 '21 at 23:14
I agree. It's not the convention. But for simplicity the solution is there. :) — Hassaan Ali, Apr 10 '21 at 00:36

score 7 · Accepted Answer · answered Apr 09 '21 at 22:23

Without changing the database structure, you could misuse a hasMany declaration to get all 4 players.

class Game extends Model
{
    public function players()
    {
        return $this->hasMany(Player::class, 'id', 'player1_id')
                    ->orWhere('id', $this->player2_id)
                    ->orWhere('id', $this->player3_id)
                    ->orWhere('id', $this->player4_id);
    }
}

class Player extends Model
{
    public function games()
    {
        return $this->hasMany(Game::class, 'player1_id', 'id')
                    ->orWhere('player2_id', $this->id)
                    ->orWhere('player3_id', $this->id)
                    ->orWhere('player4_id', $this->id);
    }
}

However that is not ideal.

You should have a third table to properly map this many to many relationship.

table 1 - players:     id (pk), name
table 2 - games:       id (pk)
table 3 - game_player: id (pk), game_id (fk), player_id (fk), unique([game_id, player_id])

class Game extends Model
{
    public function players()
    {
        return $this->belongsToMany(Player::class, 'game_player');
    }
}

class Player extends Model
{
    public function games()
    {
        return $this->belongsToMany(Game::class, 'game_player');
    }
}

This is actually exactly what I was looking for. Admittedly the more appropriate solution (and also better design) would indeed be the database normalization with the pivot table. However in the interests of the simplicity of the one-record-per-game design I will most probably opt for this approach. Many thanks. — pazof, Apr 10 '21 at 18:58
It is unfortunate that Eloquent does not have a way to do complex relationships like this. Often, we inherit old databases that we cannot redesign. — ryantxr, Aug 05 '22 at 17:15
I disagree. It would just support and foster bad database design. — IGP, Aug 05 '22 at 19:17

Laravel Eloquent relationship - multiple columns of table reference same foreign key

1 Answers1