Multi level associations in rails

I am creating an application to track football teams through out the season. but i am stuck on the design of the database. One fixture has a home team and an away team. I have created a fixture model which has two foreign keys- home_team and away_team but I cant get the association to work right. any ideas? Each fixture belongs to a league.

Solution

The simple answer is:

class Fixture < ActiveRecord::Base
  belongs_to :home_team, :class_name => "Team", :foreign_key => :home_team
  belongs_to :away_team, :class_name => "Team", :foreign_key => :away_team
end

class Team < ActiveRecord::Base
  has_many :fixtures
end

But this is no good if you because Team.fixtures will not work.

class Team < ActiveRecord::Base
  has_many :home_fixtures, :class_name => "Fixtures", :foreign_key => :home_team
  has_many :away_fixtures, :class_name => "Fixtures", :foreign_key => :away_team
end

will give you two collections… but aggregating them will have to happen in ruby, which will be icky.

class Team < ActiveRecord::Base
  def fixtures(*args)
    home_fixtures.all(*args) + away_fixtures.all(*args)
  end
end

This has it's problems too, sort and limit will be all ballsed up (heh, a pun, who knew?).

class Team < ActiveRecord::Base
  has_many :fixtures, :finder_sql => 'SELECT * FROM fixtures where (home_team = #{id} or away_team = #{id})'
  has_many :home_fixtures, :class_name => "Fixtures", :foreign_key => :home_team
  has_many :away_fixtures, :class_name => "Fixtures", :foreign_key => :away_team
end

This is ugly, but may just work. The finder_sql seems to do what's needed.

The other option is to use a named_scope:

class Fixture < ActiveRecord::Base
  named_scope :for_team_id, lambda{|team_id| {:conditions => ['(home_team = ? or away_team = ?)', team_id, team_id]} }
  belongs_to :home_team, :class_name => "Team", :foreign_key => :home_team
  belongs_to :away_team, :class_name => "Team", :foreign_key => :away_team
end

class Team < ActiveRecord::Base
  def fixtures
    Fixtures.for_team_id(id)
  end
end

This last solution is the one I'd stick with, because it's a scope it will behave much like a read only association, and prevents the :finder_sql wackiness that may happen further down the line (I mean really? How does it know how to merge more conditions? It sometimes does a subquery, a subquery? Thats just not needed here? ).

Hope this helps.