Forum: Ruby on Rails Strange Database Mapping Question

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.
Cf1cba319d6d1117bafd02f418525ad1?d=identicon&s=25 David Latham (Guest)
on 2006-05-14 08:35
(Received via mailing list)
Dear Rails List,

I am new on the list so will assume straight away that this question has
been
answered before.  As I can see no immediate way of searching the list, I
will
ask the question.  I apologize in advance for any disruption this may
cause.

Question time:

I am trying to write a web application for the staff at my office to
play a
form of virtual Super14.  Its a bit like any of the virtual sports
available
on the internet except this one is for Rugby.

The relevant tables in my database are as follows:

create table teams (
	id INT AUTO_INCREMENT NOT NULL,
	name VARCHAR(40),
	PRIMARY_KEY(id) );

create table games (
	id INT AUTO_INCREMENT NOT NULL,
	date DATETIME,		#Date of game
	home_teams_id INT,	#id of home team
	away_teams_id INT,	#id of away team
	home_team_score INT,	#this is not meant to link to another table
	away_team_score INT,	#this is not meant to link to another table
	PRIMARY_KEY (id) );

In this example the home and away team records in games are supposed to
link
to a team record in teams.

In the models, I was thinking that the game model would include,
"has_many :teams"
and the team model would include, "belongs_to :game"

When I then look at the scaffold list for Games the home and away teams
do not
show up.

Is there a better way to name the home and away team_id's in the games
table
that will fix this?
or is there another more elegant way to handle this.

Thanks for your patience reading this and I look forward to hearing from
you,

Dave Latham
@NewZealand.Auckland
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-05-14 09:04
David Latham wrote:
> I am trying to write a web application for the staff at my office to
> play a
> form of virtual Super14.  Its a bit like any of the virtual sports
> available
> on the internet except this one is for Rugby.
>
> The relevant tables in my database are as follows:
>
> create table teams (
> 	id INT AUTO_INCREMENT NOT NULL,
> 	name VARCHAR(40),
> 	PRIMARY_KEY(id) );
>
> create table games (
> 	id INT AUTO_INCREMENT NOT NULL,
> 	date DATETIME,		#Date of game
> 	home_teams_id INT,	#id of home team
> 	away_teams_id INT,	#id of away team
> 	home_team_score INT,	#this is not meant to link to another table
> 	away_team_score INT,	#this is not meant to link to another table
> 	PRIMARY_KEY (id) );
>
> In this example the home and away team records in games are supposed to
> link
> to a team record in teams.
>
> In the models, I was thinking that the game model would include,
> "has_many :teams"
> and the team model would include, "belongs_to :game"
>
> When I then look at the scaffold list for Games the home and away teams
> do not
> show up.
>
> Is there a better way to name the home and away team_id's in the games
> table
> that will fix this?
> or is there another more elegant way to handle this.

The problem you've run into is that you have two relationships from game
to team, one for home team and one for away team. Rails can't
automagically figure out what to do, so you have to tell it a bit more
than usual. You've also got the has_many and belongs_to methods in the
wrong classes, which is a common newbie mistake. But first, change the
foreign keys in games to be singular, home_team_id and away_team_id -
then you can call them game.home_team and game.away_team in your code.

class Game < AR
  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

Following your schema, a team has both home games and away games, and
the two sets are distinct.  The set of all games for a team ends up
being a union of both home and away games.

class Team < AR
  has_many :home_games, :class_name => "Game", :foreign_key =>
"home_game_id"
  has_many :away_games, :class_name => "Game", :foreign_key =>
"away_game_id"

  def games
    home_games + away_games
  end
end

That should be enough to get you started. If you want to get a bit
tricky, since your Game class can act as a join model, you could set up
a has_many :through association on Team to conveniently access all its
home or away opponents.

  has_many home_game_opponents, :through => home_games, :source =>
:away_team
  has_many away_game_opponents, :through => away_games, :source =>
:home_team

Those associations may not be all that useful as such, but you can use
them as a base to add on other conditions. (Just a caveat... the above
code isn't tested and it's late here so take it with a grain of salt.
But I'm pretty sure it should work ok.)

--
Josh Susser
http://blog.hasmanythrough.com
6dab365a82517fb694650a57ee88e4a4?d=identicon&s=25 j`ey (Guest)
on 2006-05-14 09:05
David Latham wrote:
>
> When I then look at the scaffold list for Games the home and away teams
> do not
> show up.
>
> Is there a better way to name the home and away team_id's in the games
> table
> that will fix this?
> or is there another more elegant way to handle this.
>
> Thanks for your patience reading this and I look forward to hearing from
> you,
>
> Dave Latham
> @NewZealand.Auckland

Scaffolding doesn't handle this, you'll have to code it yourself. I
suggest you look at www.rubyonrails.com for some tutorials to get you
started, if you haven't already.

j`ey
http://www.eachmapinject.com
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-05-14 17:02
Josh Susser wrote:
> class Team < AR
>   has_many :home_games, :class_name => "Game", :foreign_key =>
> "home_game_id"
>   has_many :away_games, :class_name => "Game", :foreign_key =>
> "away_game_id"
>
>   def games
>     home_games + away_games
>   end
> end

I knew I'd muff something doing a posting just before bed. Of course
those foreign keys should be team ids.

  has_many :home_games, :class_name => "Game", :foreign_key =>
"home_team_id"
  has_many :away_games, :class_name => "Game", :foreign_key =>
"away_team_id"

--
Josh Susser
http://blog.hasmanythrough.com
This topic is locked and can not be replied to.