I've got five models:
Teams have seasons, seasons have games, and games have stats, and each stat relates to a stat_meta. The stat_meta table is basically static, and it's there to classify stats by type.
I am working on a function in the Stat_Meta table so that I can call for any particular stat type (or stat_meta) to be counted within the confines of a given game, season, or team.
This is really easy for games, because I just do this:
return $this->hasMany('App\Models\Stat', 'stat')->where('game_id', 1)->count();
However, since stats are not directly related to a season or team (they are related by the game being related to both) that gets really hairy.
I know this is wrong, but I tried it (it failed) to maybe make it clearer what I am trying to do here:
return $this->hasMany('App\Models\Stat', 'stat')->where('game.season_id', 7)->count();
Doing this gave me the following error (somewhat obviously):
Column not found: 1054 Unknown column 'game.season_id' in 'where clause'
After a lot of Googling I have yet to really find anything out about this. Any thoughts?
TL;DR, withCount() - https://laravel.com/docs/5.8/eloquent-relationships#counting-related-models
Maybe you could achieve this by taking the Season, then the games then counting the stat you want. It will look something like this
$statIdenticator = 'somethingYouAreSearchingInStats';
$season = Season::with(['games' => function($q1) use($statIdenticator) {
return $q1->withCount(['stats' => function($q2) use($statIdenticator) {
return $q2->where('statIdenticator', $statIdenticator);
}]);
}])->find($seasonId);
Did not tested it, but it should give you a stats_count parameter on each games. At this point you only need to do this $season->games->sum('stats_count')
Do not copy paste, ther is not enough information about your code to be sure that will work.