CODEWITHSUNDEEP
ruby-on-railsactiverecordgroup-by
Str8WaveDave
Str8WaveDave

Reputation: 199

How to get the last nth record for each group in a rails query

I have a table named Game which is filled with individual players statlines from all their games in an nhl season. The fields That I am concerned with here are games_played which = n for the nth game of a player in the season. and name which is the players name.

Essentially I am trying to figure out how to grab the nth last game for each player.

For instance I know the following query can get me the last game for each player

Game.group(:name).having('games_played = MAX(games_played)')

However when I try the following (lets say n is 10)

Game.group(:name).having('games_played=MAX(games_played)-10')

I get no results

and in fact if I explicitly do something like 

Game.group(:name).having('games_played=16')

I only get the last game of players who played exactly 16 games, instead of the 16th game for all players. (Which I guess explains the no results of the previous query)

How do I go about getting the nth last game for each player?

Is this even the right way to query this? I also have a player table where a player has_many games, and a game belongs_to a player. Should I be taking advantage of that?

Upvotes: 3

Views: 1285

Answers (1)

Lasse Sviland
Lasse Sviland

Reputation: 1517

To find the nth last game for a specific player, it would probably be easiest to start by finding the player.

player = Player.find_by(name: "user3692508")

then you can find the players games with:

player.games

To get the nth last game, you can order it by games_played in descending order, then limit it to one result and offset it with the offset you want:

player.games.order(games_played: :desc).limit(1).offset(0)

If you do an offset of 0, you will get the last game. If you do an offset of 1 you will get the 2nd last game and so on.

This is assuming your player has_many :games and the game belongs_to :player

By using a sub query you can get the nth last game for each player.(it looks like a mess.....)

offset = 1

sub_query = "SELECT 'sub_game'.id FROM 'games' as sub_game WHERE 'sub_game'.'player_id' = games.player_id ORDER BY 'sub_game'.'games_played' DESC LIMIT 1 OFFSET #{offset}"

Game.joins(:player).where("games.id IN (#{sub_query})").order(id: :desc).group(:player_id)

With this solution, you would sort the games for each player in the sub query and do the offset there first.

Upvotes: 2

Related Questions