Rails 5 - Multiple Foreign keys belongs to the same table

Question

I have a Order model that belongs to a LoadingStation model. And the the LoadingStation will be used two times in the Order table, so it looks like:

class CreateLoadingStations < ActiveRecord::Migration[5.0]
  def change
    create_table :loading_stations do |t|
      t.integer :type
      t.string :comp_name1
      t.string :street
      t.string :street_num
      t.string :zip_code
      t.string :city

      t.timestamps
    end
  end
  end



 class CreateOrders < ActiveRecord::Migration[5.0]
      def change
         create_table :orders do |t|
          t.string :status
          t.belongs_to :loading_station, class_name: "LoadingStation", index: true, foreign_key: "loading_station_id"
          t.belongs_to :unloading_station, class_name: "LoadingStation", index: true, foreign_key: "unloading_station_id"

          t.timestamps
        end
      end
    end

When I let run rails db:migrate I got this error: ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "unloading_stations" does not exist

Mmmmmm, seems to be that the class_name was not correct detected. The class_name should be the same in both statements, correct ?

Let check the model of Loading Station:

 class LoadingStation < ApplicationRecord
end

Okay, I change the CreateOrders migration:

 t.belongs_to :loading_station, class_name: "LoadingStation", index: true, foreign_key: "unloading_station_id"

Now when I let run the rails db:migrate I got this error: ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR: constraint "fk_rails_5294e269cc" for relation "orders" already exists

Okay, I understand the Foreign Key in the database seems to be identical and the database declines the migration task.

But what is the sense of the foreign_key: option when I define who different foreign_key names, when the database detects two identical ?

Appendix:

Here my Order model:

class Order < ApplicationRecord
end

Final Question

And at the end - among the error messages - I want to have two Foreign Keys pointing to the same table.

Answer 1

I'll just focus on the code that allows you to reference the loading station table twice

In Rails 5.1 or greater you can do it like this:

Migration

class createOrders < ActiveRecord::Migration
   def change
    create_table(:orders) do |t|
        t.references :loading_station, foreign_key: true
        t.references :unloading_station, foreign_key: { to_table: 'loading_stations' }
    end
  end
end

This will create the fields loading_station_id, and unloading_station_id and make the database level references to the loading_stations table

Models

class Order < ActiveRecord::Base
  belongs_to :loading_station
  belongs_to :unloading_station, class_name: "LoadingStation"
end

class LoadingStation < ActiveRecord::Base
  has_many :load_orders, class_name: "Order", foreign_key: "loading_station_id"
  has_many :unload_orders, class_name: "Order", foreign_key: "unloading_station_id"
end

Answer 2

I think you're mixing up what should to be in the migration and what should to be in the model (and I don't mean this in a derogative way so please don't take it that way).

I have put together a rails 5 project for you to demonstrate what I think you want to achieve:

In summary I believe you're looking to have both the loading and unloading stations stored in the one table, but reference them separately as attributes of the order. In other words you're looking for single-table inheritance.

This is what I built quickly:

# app/models/order.rb
class Order < ApplicationRecord
  belongs_to :loading_station, optional: true
  belongs_to :unloading_station, optional: true
end

# app/models/station.rb
class Station < ApplicationRecord
end

# app/models/loading_station.rb
class LoadingStation < Station
  has_many :orders
end

# app/models/unloading_station.rb
class UnloadingStation < Station
  has_many :orders
end

As you can see the LoadingStation and UnloadingStation models inherit the Station model. The Station model gets a table in the database.

# db/migrate/20170826085833_create_orders.rb
class CreateStations < ActiveRecord::Migration[5.1]
  def change
    create_table :stations do |t|
      t.string :comp_name1
      t.string :street
      t.string :street_num
      t.string :zip_code
      t.string :city
      t.string :type, null: false
      t.timestamps
    end
  end
end

The type column is defined as a :string and holds the class name of the subclassed models, either LoadingStation or UnloadingStation.

And the orders table migration looks like this:

# db/migrate/20170826085833_create_orders.rb
class CreateOrders < ActiveRecord::Migration[5.1]
  def change
    create_table :orders do |t|
      t.belongs_to :loading_station, null: true, index: true
      t.belongs_to :unloading_station, null: true, index: true
      t.string :status
    end
  end
end

As you can see it references the LoadingStation.id and UnloadingStation.id. I wasn't sure whether or not these attributes were mandatory, so I set them to null: false in the column definitions and optional: true in the Order model.

To test that this works I created one LoadingStation, one UnloadingStation and one Order in the database seeds:

# db/seeds.rb
loading_station_1 = LoadingStation.create!(
  comp_name1: "Loading Station 1",
  street: "Park Ave",
  street_num: "300",
  zip_code: 10001,
  city: "NY"
)

unloading_station_4 = UnloadingStation.create!(
  comp_name1: "Unloading Station 4",
  street: "Madison Ave",
  street_num: "204",
  zip_code: 10001,
  city: "NY"
)

Order.create!(
  loading_station: loading_station_1,
  unloading_station: unloading_station_4,
  status: "delivered"
)

To test all this, simply create the database, run the migrations and execute the seeds:

rails db:create
rails db:migrate
rails db:seed

To test the result live open the rails console:

irb(main):001:0> pp Station.all

Station Load (0.3ms)  SELECT "stations".* FROM "stations"
[#<LoadingStation:0x007fcb8ac39440
  id: 1,
  comp_name1: "Loading Station 1",
  street: "Park Ave",
  street_num: "300",
  zip_code: "10001",
  city: "NY",
  type: "LoadingStation",
  created_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00,
  updated_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00>,
 #<UnloadingStation:0x007fcb8ac39288
  id: 2,
  comp_name1: "Unloading Station 4",
  street: "Madison Ave",
  street_num: "204",
  zip_code: "10001",
  city: "NY",
  type: "UnloadingStation",
  created_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00,
  updated_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00>]

irb(main):002:0> pp Order.all

Order Load (0.2ms)  SELECT "orders".* FROM "orders"
[#<Order:0x007fcb8bca2700
  id: 1,
  loading_station_id: 1,
  unloading_station_id: 2,
  status: "delivered">]

irb(main):003:0> order = Order.first

Order Load (0.2ms)  SELECT  "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> #<Order id: 1, loading_station_id: 1, unloading_station_id: 2, status:  "delivered">

irb(main):004:0> pp order.loading_station

LoadingStation Load (0.2ms)  SELECT  "stations".* FROM "stations" WHERE "stations"."type" IN ('LoadingStation') AND "stations"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
#<LoadingStation:0x007fcb8c0e4390
 id: 1,
 comp_name1: "Loading Station 1",
 street: "Park Ave",
 street_num: "300",
 zip_code: "10001",
 city: "NY",
 type: "LoadingStation",
 created_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00,
 updated_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00>

irb(main):005:0> pp order.unloading_station

UnloadingStation Load (0.3ms)  SELECT  "stations".* FROM "stations" WHERE "stations"."type" IN ('UnloadingStation') AND "stations"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
#<UnloadingStation:0x007fcb8a36a378
 id: 2,
 comp_name1: "Unloading Station 4",
 street: "Madison Ave",
 street_num: "204",
 zip_code: "10001",
 city: "NY",
 type: "UnloadingStation",
 created_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00,
 updated_at: Sat, 26 Aug 2017 09:06:53 UTC +00:00>

irb(main):006:0> pp order.status

"delivered"

I hope this helps you. I have checked the code into github, you can access it at https://github.com/JurgenJocubeit/SO-41796815.

Answer 3

I didn't get any error on creating migrations with followings:

t.belongs_to :loading_station, class_name: "LoadingStation", index: true, foreign_key: "loading_station_id"

t.belongs_to :unloading_station, class_name: "LoadingStation", index: true, foreign_key: "unloading_station_id"

and the Order table looks like:

Order(id: integer, status: string, loading_station_id: integer, unloading_station_id: integer, created_at: datetime, updated_at: datetime)

so it has necessary ids which you need!

Answer 4

It looks like you have not created a unloading_station model.