Improving this messy has_many :through with :include

Rails
1

I have a question about cleaning up a rather complex has_many. I’ll
describe the context in case it helps, but perhaps you can skip
straight to the code below.

  • I am designing a fantasy football type system.
  • I am modeling a Team which consists of many Players.
  • I am connecting Teams and Players via a Transfer join model.
  • A Transfer also has two GameWeeks (a GameWeek encapsulates the
    start_time and end_time of a period of play).
  • Transfer.from_end_of_gameweek indicates the GameWeek from which the
    Player becomes part of the Team.
  • Transfer.until_end_of_gameweek indicates the GameWeek from which a
    Player ceases to be part of the Team.

So Transfers is essentially acting as a warehouse, storing history
about all player transfers that have taken place (in the future I
might make a dedicated warehouse but not yet).

Now, to determine which Players are playing for a Team at a given
instant I have this:

class Team < ActiveRecord::Base
  has_many :transfers
  has_many :players, :through => :transfers,
    :include => [{ :transfers => :from_end_of_gameweek }, { :transfers
=> :until_end_of_gameweek }],
    :conditions => ['gameweeks.start_time <= ? AND (gameweeks.end_time
IS NULL OR ? < gameweeks.end_time)', Time.now.utc, Time.now.utc]
end

Thus I can say team.transfers to get a list of all Transfers the Team
has made, or team.players to get the current squad.

It works, but that has_many is a pretty unpleasant bit of code and the
SQL doesn’t look the most efficient. The question is, can this be made
more elegant and maybe efficient?

Thanks.