Multiple joins to same mysql table (noob question)

I am working on a site that includes soccer match scheduling. I have
the following basic mysql schema:

TABLE teams (
id
name
)

TABLE fixtures (
id
home_team_id
away_team_id
)

In the past, with mysql/php, I would have done something like this:

SELECT […] FROM fixtures, teams AS t1, teams AS t2 WHERE
home_team_id=t1.id AND away_team_id=t2.id;

I am stumped as to how to do this with RoR using the ActiveRecord
belongs_to and has_many associations. I tried something like this to no
avail:

class Fixture < ActiveRecord::Base
belongs_to :team,
:foreign_key => “home_team_id”
belongs_to :team,
:foreign_key => “away_team_id”
end

with reciprocal references in the Team.rb model, but it didn’t seem to
work.

Any thoughts would be greatly appreciated.

On 12/5/05, Mitch L. [email protected] wrote:

class Fixture < ActiveRecord::Base
belongs_to :team,
:foreign_key => “home_team_id”
belongs_to :team,
:foreign_key => “away_team_id”
end

You can’t gave two different associations the same name. You want:

class Fixture < ActiveRecord::Base
belongs_to :home_team, :class_name=>‘Team’, :foreign_key =>
“home_team_id”
belongs_to :away_team, :class_name=>‘Team’, :foreign_key =>
“away_team_id”
end

See
Peak Obsession.

Note that you won’t be able to use eager loading for both teams on
such a table without modifying Rails via a patch or plugin.