Rails - Mar 2006 - Active Record - Can''t figure out relationship.

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.





-- 
Posted via http://www.ruby-forum.com/.

Chris Sharkey 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

-- 
Posted via http://www.ruby-forum.com/.

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


-- 
Posted via http://www.ruby-forum.com/.

Hi !

2006/3/6, Josh Susser <josh@hasmanythrough.com>:
> 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
Only one thing missing - you should quote the foreign_key too.

Bye !
--
Fran?ois Beausoleil
http://blog.teksol.info/

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...


-- 
Posted via http://www.ruby-forum.com/.

[#<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...

-- 
Posted via http://www.ruby-forum.com/.

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

:foreign_id => "id"

on the end of each team!

-- 
Posted via http://www.ruby-forum.com/.

actually no, i''m completely wrong, my question stands :)

-- 
Posted via http://www.ruby-forum.com/.

On 3/6/06, Chris Sharkey <chris.sharkey@gmail.com>
wrote:
> [#<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...
>
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?

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?

-- 
Posted via http://www.ruby-forum.com/.

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

-- 
Posted via http://www.ruby-forum.com/.

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?

-- 
Posted via http://www.ruby-forum.com/.

On 3/6/06, Chris Sharkey <chris.sharkey@gmail.com>
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.

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

-- 
Posted via http://www.ruby-forum.com/.