Has_many relationship with two foreign keys

Question

In my application, a city can have many links. A link connects two cities together and is bi-directional, so the link does not have a "from" and "to." This leads to the following db schema:

  create_table "links", force: true do |t|
    t.integer "endpoint1_id"
    t.integer "endpoint2_id"
    t.integer "capacity"
  end

  create_table "cities", force: true do |t|
    t.string "name"
    t.string "lat"
    t.string "long"
  end

In my ActiveRecord models, I want to declare a relationship between two tables. Since I cannot declare two foreign keys when setting up the relationship has_many, I worked on this as follows:

class City < ActiveRecord::Base    
  # has_many with two foreign keys?
  # has_many :links
  def links
    Link.where("endpoint1_id=? OR links.endpoint2_id=?", id, id)
  end    
end

class Link < ActiveRecord::Base
  belongs_to :endpoint1, :class_name => 'City'
  belongs_to :endpoint2, :class_name => 'City'
end

This allows me to do: City.find(1).linksbut it does not seem to be the right solution and does not impose any inheritance. In addition, from linkI can not find a city of relations, if I do not go through city.endpoint1and city.endpoint2.

has_many ? - db schema?

Answer 1

, , "" , facebook, , , . , , "" , . , , , . :

• , Link.new(endpoint1_id: city_1_id, endpoint2_id: city_2_id) Link.new(endpoint2_id: city_1_id, endpoint1_id: city_2_id)

• , , sql, :

  def connections # find your cities, where connections run both ways
  City.find_by_sql "
  select * from cities
    where id in
      (select f2.endpoint1_id from links as f1 inner join links as f2 on (f1.endpoint1_id = f2.endpoint2_id) and (f1.endpoint2_id = f2.endpoint1_id) and (f1.endpoint1_id = #{id.to_i}))" # normally this can cause an sql injection, but here it is handled by to_i
  end
  

, , .