Active Record - Can't figure out relationship

Rails
1

I have the following two tables:

create table teams (
id int not null auto_increment,
short_name varchar(12) not null,
long_name varchar(50) not null,
logo varchar(20) not null,
primary key (id)
);

create table rounds (
id int not null auto_increment,
home_team_id int not null,
away_team_id int not null,
round tinyint(4) not null,
home_team_score tinyint(8) not null,
away_team_score tinyint(8) not null,
constraint fk_home_teams foreign key (home_team_id) references
teams(id),
constraint fk_away_teams foreign key (away_team_id) references
teams(id),
primary key (id)
);

And I need to be able to access this data through a join, ie I want to
be able to do things like:

@round1 = Round.get_round(1)

And then be able to access

@roind1.home_team_score
@round1.home_team.short_name
@round1.away_team.short_name

etc

But I just can’t figure out how to use belongs_to and has_many to get
this relationship going because i’m using TWO FOREIGN KEYS to join the
SAME TABLE…

I am very new to rails, sorry if this is a stupid question, but any help
someone could provide would be greatly appreciated.

Chris.

2

Chris S. wrote:

But I just can’t figure out how to use belongs_to and has_many to get
this relationship going because i’m using TWO FOREIGN KEYS to join the
SAME TABLE…

This isn’t too hard, you just didn’t dig deep enough in the docs to find
it. Look at the rdoc for
ActiveRecord::Associations::ClassMethods#belongs_to and #has_many

This should work:

class Team < ActiveRecord::Base
has_many :home_rounds, :foreign_key => home_team_id
has_many :away_rounds, :foreign_key => away_team_id
end

class Round < ActiveRecord::Base
belongs_to :home_team, :class_name => “Team”
belongs_to :away_team, :class_name => “Team”
end

–josh
http://blog.hasmanythrough.com

3

Oops, my finger twitched and I left something out. Use this instead:

class Team < ActiveRecord::Base
has_many :home_rounds, :class_name => “Round”, :foreign_key =>
home_team_id
has_many :away_rounds, :class_name => “Round”, :foreign_key =>
away_team_id
end

class Round < ActiveRecord::Base
belongs_to :home_team, :class_name => “Team”
belongs_to :away_team, :class_name => “Team”
end

–josh
http://blog.hasmanythrough.com

4

Hi !

2006/3/6, Josh S. [email protected]:

belongs_to :home_team, :class_name => “Team”
belongs_to :away_team, :class_name => “Team”
end

Only one thing missing - you should quote the foreign_key too.

Bye !

5

[#<Round:0xb774da44 @attributes={“home_team_id”=>“14”,
“away_team_score”=>“0”, “home_team_score”=>“0”, “away_team_id”=>“12”,
“id”=>“1”, “round”=>“1”}>, #<Round:0xb774d9f4
@attributes={“home_team_id”=>“6”, “away_team_score”=>“0”,
“home_team_score”=>“0”, “away_team_id”=>“8”, “id”=>“2”, “round”=>“1”}>,
#<Round:0xb774d9b8 @attributes={“home_team_id”=>“1”,
“away_team_score”=>“0”, “home_team_score”=>“0”, “away_team_id”=>“9”,
“id”=>“3”, “round”=>“1”}>, #<Round:0xb774d97c
@attributes={“home_team_id”=>“3”, “away_team_score”=>“0”,
“home_team_score”=>“0”, “away_team_id”=>“2”, “id”=>“4”, “round”=>“1”}>,
#<Round:0xb774d940 @attributes={“home_team_id”=>“5”,
“away_team_score”=>“0”, “home_team_score”=>“0”, “away_team_id”=>“4”,
“id”=>“5”, “round”=>“1”}>, #<Round:0xb774d904
@attributes={“home_team_id”=>“11”, “away_team_score”=>“0”,
“home_team_score”=>“0”, “away_team_id”=>“13”, “id”=>“6”, “round”=>“1”}>,
#<Round:0xb774d8c8 @attributes={“home_team_id”=>“0”,
“away_team_score”=>“0”, “home_team_score”=>“0”, “away_team_id”=>“7”,
“id”=>“7”, “round”=>“1”}>, #<Round:0xb774d88c
@attributes={“home_team_id”=>“10”, “away_team_score”=>“0”,
“home_team_score”=>“0”, “away_team_id”=>“15”, “id”=>“8”, “round”=>“1”}>]

this is the data I am getting…

6

I got it working I just had to change my Round model to have

:foreign_id => “id”

on the end of each team!

7

actually no, i’m completely wrong, my question stands :slight_smile:

8

Hi Josh and Francois,

Thanks very much for your help, I set it up exactly as you said and got
no errors or anything but unfortunately when I run this:

def self.get_round(round)
find(:all, :conditions => [“round = ?”, round])
end

I am getting no information about the teams at all, is there something
further I need to do, when I set up another relationship between two
other tables, the data was just there for me…

9

well each round has more than one MATCH, maybe I need to have another
seperate table to store each match for each round, would this help?

also, it still doesn’t solve the problem of getting the team data…or
is this a side effect of having multiple results for each round?

10

On 3/6/06, Chris S. [email protected] wrote:

#<Round:0xb774d940 @attributes={“home_team_id”=>“5”,

this is the data I am getting…

That indicates that you’ve got more than one Round with the same ‘round’
value.
Is that what you intended?
Does:
@round = Round.find_by_round(1)
…work the way you’d expect?

11

OK to sum up, despite putting in the belongs_to and has_many
relationship, when I try and get the round information the query
generated is this:

SELECT * FROM rounds WHERE (round = 1)

the query i’m HOPING to achieve would be something like this.

SELECT home_team.short_name as hsn, away_team.short_name as asn, r.*
FROM rounds r, teams home_team, teams away_team WHERE r.round = 1 AND
home_team.id = r.home_team_id AND away_team.id = away_team_id

Does that make more sense now?

12

yep that’s definitely the problem alright, thanks Wilson, I’ll get it
sorted then post again when it works!

13

Thanks very much Wilson, I will try both of these methods this arvo and
let you know how I go!

14

On 3/6/06, Chris S. [email protected] wrote:

yep that’s definitely the problem alright, thanks Wilson, I’ll get it
sorted then post again when it works!

What about?
class Team
has_many :matches
end

class Match
belongs_to :round
belongs_to :home_team, :class_name => “Team”
belongs_to :away_team, :class_name => “Team”
end

class Round
has_many :matches
end

Another alternative would be to use has_many :through (from the Edge
version of Rails).
With this, you could say that:
“Rounds have many Teams through Matches”
“Teams have many Rounds through Matches”
…which might be closer to what you’re trying to express.