So, I'm developing a sports system where games
table has 2 relations for the same table teams
. I know the context usuallty doesn't matter, but I'm trying to make it clear why the database is stuctured that way. The relations are stored as games.home_id references teams.id
and games.away_id references teams.id
to link the 2 teams in the same game. The tables structures are
- games
- id
- home_id
- away_id
- starts_at
- teams
- id
- team_players
- id
- team_id
So, if I want to get all the players of the games to be played today I'll do
SELECT team_players.*
FROM team_players
JOIN teams ON (teams.team_id = team_players.team_id)
JOIN games ON (teams.id = games.home_id OR teams.id = games.away_id)
WHERE games.starts_at <= $starts AND games.starts_at >= $ends
How do I create the hasMany
relationships in the models to include both (team.id = games.home_id OR team.id = games.away_id)
?
I already tried something like
class Team {
public function game()
{
return $this->hasMany(Game::class);
}
}
class Game {
public function teams()
{
$rel = $this->hasMany(Game::class, 'home_id');
$rel->orHasMany(Game::class, 'home_id');
return $rel;
}
}
but there is no orHasMany()
;
Thank you.
I think you can define two has-many-through
relationships and merge them, it is not tested, but I think it can be the solution for you, please test it and let me know, also you may need to change a little bit about the keys, I have just written to give you some idea
class Game extends Model
{
public function homeTeamPlayers()
{
return $this->hasManyThrough(
'App\TeamPlayer',
'App\Team',
'id', // Foreign key on teams table...
'team_id', // Foreign key on team_players table...
'home_id', // Local key on games table...
'id' // Local key on teams table...
);
}
public function awayTeamPlayers()
{
return $this->hasManyThrough(
'App\TeamPlayer',
'App\Team',
'id', // Foreign key on teams table...
'team_id', // Foreign key on team_players table...
'away_id', // Local key on games table...
'id' // Local key on teams table...
);
}
public function teamPlayers()
{
$this->homeTeamPlayers->merge($this->awayTeamPlayers);
}
}
now, for every game you can retrieve like
$game = Game::find(1);
$game->teamPlayers();