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.
