mysqllaraveleloquentforeign-keysrelationship

Multiple foreign keys from one table to same field in another table in Laravel


I am working on a project in Laravel where I have two table. - "teams" (table) with "Team" (model) - "players" (table) with "Player" (model) Users can pick any combination of 12 players to make their own team so I am trying to create "one to many" relationship where players will have only one record for each player but many teams can have same player in them. "players" table have column "id" as primary key. "teams" table have 12 columns like "player1_id", "player2_id", "player3_id" and so on and every column will act as a foreign key of the "id" in players table.

My clear cut question is - what code do I need to put in both models (Player and Team) so I can access the information through Eloquent.

so far I have put this code in my teams table but not sure if this is the right thing to do. for the players table, I have no clue what to write in there.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Team extends Model
{

//
public function player1()
{
    return $this->belongsTo('App\Player', 'player1_id');
}

public function player2()
{
    return $this->belongsTo('App\Player', 'player2_id');
}

public function player3()
{
    return $this->belongsTo('App\Player', 'player3_id');
}

public function player4()
{
    return $this->belongsTo('App\Player', 'player4_id');
}

public function player5()
{
    return $this->belongsTo('App\Player', 'player5_id');
}

public function player6()
{
    return $this->belongsTo('App\Player', 'player6_id');
}

public function player7()
{
    return $this->belongsTo('App\Player', 'player7_id');
}

public function player8()
{
    return $this->belongsTo('App\Player', 'player8_id');
}

public function player9()
{
    return $this->belongsTo('App\Player', 'player9_id');
}

public function player10()
{
    return $this->belongsTo('App\Player', 'player10_id');
}

public function player11()
{
    return $this->belongsTo('App\Player', 'player11_id');
}

public function bonus_player()
{
    return $this->belongsTo('App\Player', 'bonus_player_id');
}
}

Solution

  • There are several ways to do it:

    1- You can put the team players in a separate table, let's say team_players, with columns, team_id, player_id, and position. validate in your code that always 12 players are present. This would be a many-to-many relationship.

    in this scenario, you can use this code in your Team model:

    public function players() {
      return $this->belongsToMany(Player::class, 'team_players');
    }
    

    Handling the logic in code can be a bit troubling for a laravel beginner though.

    2- You can simply perform a query based on the order:

    public function player(int $position){
      $playerId = $this->attributes('player'.$position.'_id');
      return Player::find($player);
    }
    

    3- you can define magic methods, although I strongly discourage you from doing so.

    public function __call(string $name , array $arguments){
      if (Str::beginsWith($name, 'player')){
        return $this->belongsTo(Player::class, $name.'_id');
      }
    }
    

    there might be other ways too, but I suspect they'd be a little messier.