Forum: Ruby on Rails Active Record - Can't figure out relationship.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (Guest)
on 2006-03-07 02:01
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.
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-03-07 02:34
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
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-03-07 02:36
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
3dd4b52a0946bd698b1d1635a46ea3a3?d=identicon&s=25 Francois Beausoleil (Guest)
on 2006-03-07 03:17
(Received via mailing list)
Hi !

2006/3/6, Josh Susser <josh@hasmanythrough.com>:
>   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 !
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:22
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...
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:24
[#<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...
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:41
I got it working I just had to change my Round model to have

:foreign_id => "id"

on the end of each team!
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:43
actually no, i'm completely wrong, my question stands :)
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-03-07 04:45
(Received via mailing list)
On 3/6/06, Chris Sharkey <chris.sharkey@gmail.com> 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?
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:48
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?
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:50
yep that's definitely the problem alright, thanks Wilson, I'll get it
sorted then post again when it works!
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 04:59
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?
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-03-07 05:09
(Received via mailing list)
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.
Cb6ecd950a2b6a39f93f16af2ad9aa22?d=identicon&s=25 Chris Sharkey (ctp6360)
on 2006-03-07 05:11
Thanks very much Wilson, I will try both of these methods this arvo and
let you know how I go!
This topic is locked and can not be replied to.