Ad

Eloquent Relationship Structure For League App

- 1 answer

I've been working on an Angular app with a Laravel rest API and have realised my relationships aren't quite as they should be.

I have 3 entities - seasons, divisions and teams.

Season:

A season has many divisions, and many teams.

Division:

A division belongs to many seasons, and has many teams.

Team:

A team belongs to many divisions, and belongs to many seasons.

This is because a division may not be used for every season, and a team may change divisions each season, or may not play in all seasons.

I'm struggling to understand how to implement the relationship logic.

For example, I'd like to get divisions for a season and the teams present in that division for that particular season, be it the current one or when the user is viewing an old season.

Here's what I've got at the moment:

Season model

class Season extends Model
{
    protected $guarded = [];
    protected $hidden = ['pivot'];


    /**
     * Return divisions for this season
     *
     * @return BelongsToMany
     */
    public function divisions()
    {
        return $this->belongsToMany('App\Division');
    }

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

}

Division Model

class Division extends Model
{
    protected $guarded = [];

    public function matches()
    {
        return $this->hasMany('App\Fixture');
    }

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

    public function teams() {
        return $this->hasMany('App\Team');
    }

}

Team Model

class Team extends Model
{
    protected $guarded = [];
    protected $hidden = ['pivot'];

    public function division() {
        return $this->belongsTo('App\Division');
        // should be belongsToMany
    }

    public function seasons()
    {
        return $this->belongsToMany('App\Season')->select('season_id');
    }
}

With the following tables:

  • seasons
  • divisions
  • teams
  • division_season
  • season_team

But this doesn't enable me to have a team belonging to a different division per season.

If I change the division() method on the team model to be divisions() and with a belongsToMany() and have a new table - division_team (is this the right approach?) how would I then query all teams by their division on a per season basis?

Eg:

Get all divisions and their teams by season id

Bearing in mind teams have the potential to change divisions each season.

EDIT

As per answer from Thomas Van Der Veen's answer below, I have added a table division_season_team and used the relations in his answer.

Trying to get divisions with their teams based on a season id though is proving difficult - the below returns the correct divisions, but the teams aren't necessarily part of the current season!

DivisionsController

if ($request->query('seasonId')) {
    $seasonId = $request->query('seasonId');
    return $this->respond(new DivisionCollection(Division::with('teams')->whereHas(
     'seasons', function($q) use ($seasonId) {
         $q->where('season_id', '=', $seasonId);
        })->get()
    ));
}
Ad

Answer

You just don't use the correct query. Your query should be:

if ($seasonId = $request->input('seasonId')) {
    $season = Season::findOrFail($seasonId);

    $divisionIdsQuery = $season->divisions()
        ->select('divisions.id')
        ->groupBy('divisions.id')
        ->getQuery(); // can't just ->get(), because MYSQL FULL GROUP BY.

    $divisions = Division::whereIn('id', $divisionIdsQuery)
        ->with(['teams' => function ($query) use ($season) {
            $query->where('season_id', $season->id);
        }])
        ->get();

    return $this->respond(new DivisionCollection($divisions));
}
Ad
source: stackoverflow.com
Ad