Fianite Fianite - 2 months ago 16
MySQL Question

Rails getting data through multiple models?

I'm working with rails and I have this predicament:

My associations setup is like this:

Team -> Player -> Match -> Game -> PlayerStats

as in, teams have players, players have matches, matches are best of five and have games, and each player in the game has stats.

However, if I want to get a players total goals I'd have to do loop through all their matches and for every match look at every game and pull their stats from each game.

Will this be too taxing if I do this for multiple stats when I load a page? Is there perhaps a better way to approach the problem?

Answer

Your schema should look like this

Team(id, name)
Player(id, name)
Match(id, name)
Game(id, name, match_id)
PlayerStat(id, match_id, team_id, player_id, game_id, goal_count)

For players total goals query will be

player.player_stats.sum(:goal_count)

which is same as

PlayerStat.where(player_id: 2).sum(:goal_count)