Using Nested Intermediate Tables

Question

I'm working with Laravel 4 and trying to setup a table structure for handling the following problem.

I have 3 tables: Players, Teams & Seasons
For each season, I will have multiple teams assigned and each team will have multiple players assigned.
I need to maintain historical data for each season, so I can't just connect the tables directly because changing the base player/teams tables would affect all seasons that way.

I connected the Seasons -> Teams table by using an intermediate table teams_in_season as follows:

class Season extends \Eloquent
{
    public function teams()
    {
        return $this->belongsToMany('Team', 'teams_in_season');
    }
}

That works as expected. The issue comes when I want to setup the player assignment. Naturally, I want to relate the teams to players so my line of thinking is that I need to create an intermediate table off of another intermediate table. Ex:

seasons -> teams_in_season -> players_in_teams -> players

If I went Seasons -> Players, that would work except that I wouldn't be able to eager load it that way.

seasons -> players_in_season -> players

$season->teams->players->get();

Essentially, the way the user enters data is to create a season, assign teams, assign players to teams, and then eventually add scoring. All data entered needs to be maintained and therefore the intermediate tables are necessary.

So, here's my question(s):

Can I nest/chain intermediate tables like this?
Is there a better way I can setup what I want to achieve?

score 1 · Answer 1 · answered Dec 03 '13 at 20:07

This is a difficult problem. You will need to associate the players to the pivot table between teams and seasons. For that reason I would probably set that up as it's own model.

Relationships

Season hasMany SeasonTeam
SeasonTeam belongsTo Team
SeasonTeam belongsToMany Player

Eager Loading

Here is how you would list all the teams with their player roster using strictly the relationship methods and eager loading. This is 4 queries.

$season = Season::with('seasonTeams.players', 'seasonTeams.team')->find(1);

foreach ($season->seasonTeams as $seasonTeam)
{
    echo $seasonTeam->team->name;

    foreach ($seasonTeam->players as $player)
    {
        echo $player->name;
    }
}

Joins

I won't draw out the details here but you could also use joins in the query builder to pull players that were part of a team playing in a given season. Check out the link below.

http://four.laravel.com/docs/queries#joins

@Colin - Thanks for the quick reply. I'm revising my code to see if that will work for me. Using this method, you have SeasonTeam belonging to the Players table, but since I will need the players to be instanced as well, would you suggest that I create another Pivot table between SeasonTeam and Players (let's call it SeasonPlayers for example)? — Jeremy, Dec 03 '13 at 20:31
Yes, the belongsToMany relationship uses a pivot table between the two entities. You don't need a model to represent that but you could. SeasonTeam is a little abnormal but I think it's a good solution in this instance. These are the tables I had in mind: seasons, seasonteams, teams, players, player_seasonteam — Collin James, Dec 03 '13 at 21:12

score 0 · Accepted Answer · answered Dec 04 '13 at 16:19

I attempted a few different ideas (including Colin's answer), but decided to go a different route here. The main thing is that I felt that using $season->seasonTeams->teams() was a bit difficult to read. Also, attempting to chain an intermediary table off of another intermediary table felt a bit odd as well. The solution I came up with was this:

For teams, I stayed with my original design of seasons -> teams_in_seasons -> teams which allows me to lookup the instanced teams using $season->teams() and any pivot data necessary therein using the following relationship for the seasons model:

public function teams()
{
    return $this->belongsToMany('Team', 'teams_in_season');
}

and inverse on teams:

public function seasons()
{
    return $this->belongsToMany('Season', 'teams_in_season');
}

For players, instead of chaining off of the existing pivot table teams_in_season, I decided instead to connect the pivot table players_in_teams to both the seasons and teams table. To connect these together, I decided to use the Query Builder to build my own queries as follows on the Team model:

public function players($season_id, $team_id)
{
    return DB::table('players')
        ->select(DB::raw('players.*'))
        ->join('players_in_team', 'players.id', '=', 'players_in_team.player_id')
        ->where('season_id', '=', $season_id)
        ->where('team_id', '=', $team_id)
        ->orderBy('last_name');
}

this will allow me to eager load the players for a given season team as follows:

$team->players($season->id, $team->id)->get()

This is a bit unorthodox, perhaps, but it works well for my purposes. I couldn't find another method that made as much sense logically to me. It also has the side effect of allowing me to add a method onto the Seasons model that will allow me to eager load all of the players for a given season such as:

Season::find(1)->players

You can bring this back into the normal routine without much work. Season::players() and Team::players() are both $this->hasMany('Player'). Beyond that look at using scopes to encapsulate some of this into custom query builder methods. http://laravel.com/docs/eloquent#query-scopes Player::season(1)->team(5)->get(); Season::find(1)->players()->team(5)->get(); — Collin James, Dec 05 '13 at 23:39
Interesting, I haven't read up on scopes yet but I think that is the missing piece here. A combination of that and the method I chose to go sounds really good. Thanks again! — Jeremy, Dec 06 '13 at 01:25

Using Nested Intermediate Tables

2 Answers2