Topic: Cleanest way to handle subquery joins
I have been struggling with the cleanest or most railsy way to codify a query involving a subquery join. The query (1) is probably the best way to describe the goal: getting a list of all wins after the most recent loss, where the Result object has: game_id, winner_id, loser_id, created_at.
My problem is that it feels dirty to include a raw block of sql, however AR abstraction seems to make the code more complex and less readable (2). Perhaps you folks can help me chose the right level of abstraction.
(1) The original query
select
winner_id as player_id,
count(1) as winning_streak
from
results
inner join
(
select
game_id,
loser_id as player_id,
max(created_at) as loss_created_at
from
results
where
game_id = #{game.id}
group by
game_id,
loser_id
) recent_losses on winner_id = player_id and results.game_id = recent_losses.game_id
where
results.created_at > loss_created_at
group by
winner_id(2) My cleanest refactoring
recent_losses_sql = Result.select('game_id, loser_id as player_id, max(created_at) as loss_created_at').
group('game_id, loser_id').to_sql
winning_streaks = Result.select('winner_id, count(1) as winning_streak').
joins("inner join (#{recent_losses_sql}) recent_losses on player_id = winner_id and recent_losses.game_id = results.game_id").
where(:game_id => game).
where('created_at > loss_created_at').
group(:winner_id)