Defining two references to the same column in another table

Question

There's a Team table in my project. I've made a migration that is supposed to create table Match using the command below:

rails generate model Match Team:references Team:re
ferences score1:integer score2:integer date:datetime length:integer place:string

I want my Matches table to contain 2 foreign keys (team1, team2) referencing same column (id) on Teams table. I'm pretty sure I did this wrong, because in schema.rb there's:

 create_table "matchs", force: true do |t|
    t.integer  "Team_id"
    t.integer  "score1"
    t.integer  "score2"
    t.datetime "date"
    t.integer  "length"
    t.string   "place"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "matchs", ["Team_id"], name: "index_matchs_on_Team_id"

and I can't see 2nd Team_id. What is the proper way to do what I need?

Two `Foreign keys` of the same table? How could it be possible? — Pavan, Apr 17 '14 at 11:44
You cant have two `team_id`s.Probably you can have `team_1_id` and `team_2_id`. — Pavan, Apr 17 '14 at 11:59

score 5 · Accepted Answer · answered Apr 17 '14 at 15:09

A database table cannot have two columns with the same name. This is what you'll need in order to get this to work. (I'm going to use home_team and away_team to help distinguish them, but obviously you could use team1 and team2.)

rails g migration AddTeamsToMatch home_team_id:integer away_team_id:integer

This will generate a migration that looks like this:

class AddTeamsToMatch < ActiveRecord::Migration
  def change
    add_column :matches, :home_team_id, :integer
    add_column :matches, :away_team_id, :integer
  end
end

Then in your Team model, you can have the following:

class Team < ActiveRecord::Base
  has_many :home_matches, class_name: "Match", foreign_key: "home_team_id"
  has_many :away_matches, class_name: "Match", foreign_key: "away_team_id"

  # Get all matches
  def matches
    self.home_matches + self.away_matches
  end
end

In your Match model, you can have:

class Match < ActiveRecord::Base
  belongs_to :home_team, class_name: "Team"
  belongs_to :away_team, class_name: "Team"

  # List both teams as array
  def teams
    [self.home_team, self.away_team]
  end
end

Hopefully this helps.

Defining two references to the same column in another table

1 Answers1

Linked