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:
<pre> 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 </pre>
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.
