Strange Database Mapping Question


#1

Dear Rails L.,

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


#2

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 S.
http://blog.hasmanythrough.com


#3

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


#4

Josh S. 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 S.
http://blog.hasmanythrough.com