Complex many-to-many relation in Rails

Question

I have a model Place. For instance, place can be a city, a district, a region, or a country, but I think one model will be enough, because there is no big difference between city and region for my purposes.

And places can belong to each other, e.g. a region can have many cities, a country can have many regions etc.

But my problem is that it's kind of historical project and a city can belong to many regions. For example, from 1800 to 1900 a city belonged to one historical region (that doesn't exist now) and from 1900 till now - to another. And it's important to store those regions as different places despite of they can have similar geographical borders but only names are different.

I guess it's many-to-many, but maybe someone can give a better idea?

And if it's many-to-many, how can I get a chain of parent places in one query to make just simple string like "Place, Parent place 1, Parent place 2, Parent place 3", e.g. "San Francisco, California, USA"?

Here is my code:

create_table :places do |t|
  t.string :name

  t.timestamps null: false
end

create_table :place_relations, id: false do |t|
  t.integer :sub_place_id
  t.integer :parent_place_id

  t.timestamps null: false
end

class Place < ActiveRecord::Base
  has_and_belongs_to_many :parent_places,
    class_name: "Place",
    join_table: "place_relations",
    association_foreign_key: "parent_place_id"

  has_and_belongs_to_many :sub_places,
    class_name: "Place",
    join_table: "place_relations",
    association_foreign_key: 'sub_place_id'
end

Please, don't hesitate to give me some ideas about it!

Answer 1

This makes a association of direct many to many relation with another model without intervening that model . But you can use more advanced stuff if you want like Polymorphic Association .

For More Information please visit Rails Guide : Polymorphic Association

Answer 2

This is the first solution that popped in my mind, and there may be many other ways to do it, but I believe this may arguable be the cleanest.

You've got the right general idea, but all you need is a slight modification to the join table. Essentially you'll use has_many... through relationships instead, so that you can append some kind of time frame discriminator.

In my examples, I'm using a datetime field to indicate from what point the association is relevant. In combination with a default scope to order the results by the time discriminator (called effective_from in my examples), you can easily select the "current" parents and children of a place without additional effort, or select historical data using a single date comparison in the where clause. Note that you do not need to handle the time frame discrimination as I did, it is merely to demonstrate the concept. Modify as needed.

class PlaceRelation < ActiveRecord::Base
  default_scope { order "effective_from DESC" }

  belongs_to :parent, class_name: "Place"
  belongs_to :child, class_name: "Place"
end

class Place < ActiveRecord::Base
  has_many :parent_places, class_name: "PlaceRelation", foreign_key: "child_id"
  has_many :child_places, class_name: "PlaceRelation", foreign_key: "parent_id"

  has_many :parents, through: :parent_places, source: :parent
  has_many :children, through: :child_places, source: :child
end

and the migration for the place_relations table should look like this:

class CreatePlaceRelations < ActiveRecord::Migration
  def change
    create_table :place_relations do |t|
      t.integer :parent_id
      t.integer :child_id
      t.datetime :effective_from
      t.timestamps
    end
  end
end

So if we create a couple of "top level" country-places:

country1 = Place.create(name: "USA")
country2 = Place.create(name: "New California Republic")

and a state place

state = Place.create("California")

and a city

city = Place.create("San Francisco")

and finally tie them all together:

state.parent_places.create(parent_id: country1.id, effective_from: DateTime.now - 1.year)
state.parent_places.create(parent_id: country2.id, effective_from: DateTime.now)

city.parent_places(parent_id: state.id, effective_from: DateTime.now)

Then you would have a city ("San Francisco") which belongs to the state "California", which historically belonged to the country "USA", and later "New California Republic".

Additionally, if you would like to build a string containing the place's name and all its "parents", you could do it "recursively" like so:

def full_name(name_string = [])
  name_string << self.name
  parent = self.parents.first
  if parent.present?
    return parent.full_name name_string
  else
    return name_string.join(", ")
  end
end

Which, in the case of our city "San Francisco", should result in "San Francisco, California, New California Republic", given the ordering of the effective_from field.