Eloquent Relationship referencing multiple foreign keys

Hi there, recently I came up with the idea of making some standings in sports. So then I was going to create my migrations but I faced an issue, because I had in a games table a home_id and a away_id referencing the different competitors This is my database structure:

Competition: 
    name 
	organizator
	sport
	description
	user_id

Competitor:
	name
	competition_id

Game:
	home_id
	away_id
	date
	home (score)
	away

So i want to get all games of a single competitor, but I have no idea how I can get all games, referencing both home_id and away_id Appreciate every help

lbecket

3 years ago

Level 39

To get all the games of a single competitor, you will need to query the Game table for all games in which the competitor is either the home or away team. You can achieve this by using Eloquent relationships.

First, define the relationships between your models in your models' classes. In the Game model, define the relationships to the Competitor model twice, once for home and once for away:

class Game extends Model
{
    public function homeTeam()
    {
        return $this->belongsTo(Competitor::class, 'home_id');
    }

    public function awayTeam()
    {
        return $this->belongsTo(Competitor::class, 'away_id');
    }
}

In the Competitor model, define the relationship to the Game model:

class Competitor extends Model
{
    public function games()
    {
        return $this->hasMany(Game::class, 'home_id')->orWhere('away_id', $this->id);
    }
}

This defines a games relationship that retrieves all games where the competitor is either the home or away team.

Now, you can use this relationship to retrieve all games for a single competitor. For example, to retrieve all games for a competitor with an ID of 1:

$competitor = Competitor::find(1);
$games = $competitor->games;

This will retrieve all games where the competitor with an ID of 1 is either the home or away team.

1 like

godofclash

3 years ago

Level 1

@lbecket somehow it doesn't work with eloquents with method, it then return an empty array, do you know any solution to that?

Please or to participate in this conversation.