CODEWITHSUNDEEP
phplaraveleloquenteloquent-relationship
pazof
pazof

Reputation: 964

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

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(){

        //?

    }
}

Upvotes: 3

Views: 4927

Answers (1)

IGP
IGP

Reputation: 15786

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');
    }
}

Upvotes: 9

Related Questions