Using ActiveRecord relationships to contain multiple instances of an object per row

Question

I am trying to figure out how to use ActiveRecord relationships to relate a model that can contain multiple instances of another model within one row. For example, having has_many :dogs in one model, and belongs_to :dog in the other means that in the one model, "dog_id" will refer to an instance of :dog. However, I want to be able to have multiple instances of :dog within my related (has_many) model, such as dog_id1, dog_id2, dog_id3, etc. See the code below to understand what I mean. How can I do this?

I have the following models:

--tp.rb
class Tp < ActiveRecord::Base
  has_many :dogs
  has_many :cats
  has_many :stars
  attr_accessible :dog_id1, :dog_id2, :dog_id3, :dog_id4, :cat_id1, :cat_id2, :cat_id3, :cat_id4, :star_id, :tp_id
end

--dog.rb
class Dog < ActiveRecord::
  belongs_to :tp
  attr_accessible :dog_id, :dog_name
end

--cat.rb
class Cat < ActiveRecord::Base
  belongs_to :tp
  attr_accessible :cat_id, :cat_name
end

--star.rb
 class Star < ActiveRecord::Base
   belongs_to :tp
   attr_accessible :patient_id
 end

Answer 1

I think you have your belongs_to/has_many a little backwards.

belongs_to and has_many

For your example,

class Tp < ActiveRecord::Base
  has_many :dogs
  has_many :cats
  has_many :stars
end

Tp doesn't have any dog_id, cat_id, or star_id in its database row. When you set belongs_to in the other models,

class Dog < ActiveRecord::Base
  belongs_to :tp
end

class Cat < ActiveRecord::Base
  belongs_to :tp
end

class Star < ActiveRecord::Base
  belongs_to :tp
end

You should add a tp_id to each model (dogs table, cats table, and stars table).

Then, calling

tp = Tp.find(123)

Finds Tp with id equal to 123

SELECT * FROM tps WHERE id = 123;

And calling

cats = tp.cats
dogs = tp.dogs
stars = tp.stars

Finds all Cat, Dog, and Star instances with tp_id equal to 123

SELECT * FROM cats WHERE tp_id = 123;
SELECT * FROM dogs WHERE tp_id = 123;
SELECT * FROM stars WHERE tp_id = 123;

If you need your Cat instances to belong to many Tp instances, and Tp instances to have many Cat instances, then you should look at Rails's has_and_belongs_to_many or has_many :through.

has_and_belongs_to_many

A has_and_belongs_to relationship would require new tables cats_tps, dogs_tps, and stars_tps. These tables would have a schema of

cats_tps
  cat_id
  tp_id
dogs_tps
  dog_id
  tp_id
stars_tps
  star_id
  tp_id

Then in your models

class Tp < ActiveRecord::Base
  has_and_belongs_to_many :dogs
  has_and_belongs_to_many :cats
  has_and_belongs_to_many :stars
end

class Dog < ActiveRecord::Base
  has_and_belongs_to_many :tps
end

class Cat < ActiveRecord::Base
  has_and_belongs_to_many :tps
end

class Star < ActiveRecord::Base
  has_and_belongs_to_many :tps
end

Now, running

tp = Tp.find(123)
cats = tp.cats

Generates the SQL

SELECT "cats".* FROM "cats" INNER JOIN "cats_tps" ON "cats"."id" = "cats_tps"."cat_id" WHERE "cats_tps"."tp_id" = 123;

Which is essentialy the query (get me a list of all the cat_ids that belong to Tp 123) and then (get me all the cats that match these cat ids).

Generating the cats_tps join table can be done with a migration like

class CreateCatsTps < ActiveRecord::Migration
  def change
    create_table :cats_tps, :id => false do |t|
      t.belongs_to :cat
      t.belongs_to :tp
    end
  end
end

This works great for simple joins, but you may want to look into using has_many :through. This is because the cats_tps table holds no information about when or why this Cat belongs to a Tp or this Tp belongs to the Cat. Likewise, if you add Bird, Horse, Frog, and Snake models, you will have to create birds_tps, horses_tps, frogs_tps, and snakes_tps tables. Yuck.

has_many :through

To create a has_many :through relationship, you create a new model that makes sense semantically that links a Tp to a Cat. For instance, let's say that a Tp walks cats. You could create an Walk model that links a Cat to a Tp.

class Walk < ActiveRecord::Base
  belongs_to :cat
  belongs_to :tp
  attr_accessible :price, :duration, :interval # these attributes describe the Walk relationship
end

class Cat < ActiveRecord::Base
  has_many :walks
  has_many :tps, :through => :walks
end

class Tp < ActiveRecord::Base
  has_many :walks
  has_many :cats, :through => :walks
end

Now, the relationship is similar to a has_and_belongs_to_many, but you can include metadata about the walking relationship. Additionally, say that a Tp also walks dogs. You could convert the belongs_to :cat into a polymorphic belongs_to :animal relationship so that a Tp can walk a cat, dog, mouse, rabbit, horse, ... you name it.

class Walk < ActiveRecord::Base
  belongs_to :animal, :polymorphic => true
  belongs_to :tp
  attr_accessible :price, :duration, :interval # these attributes describe the Walk relationship
end

class Cat < ActiveRecord::Base
  has_many :walks, :as => :animal
  has_many :tps, :through => :walks
end

class Dog < ActiveRecord::Base
  has_many :walks, :as => :animal
  has_many :tps, :through => :walks
end

class Tp < ActiveRecord::Base
  has_many :walks
  has_many :cats, :through => :walks, :source => :animal, :source_type => 'Cat'
  has_many :dogs, :through => :walks, :source => :animal, :source_type => 'Dog'
end

This relationship is created with a migration like

class CreateWalks < ActiveRecord::Migration
  def change
    create_table :walks do |t|
      t.belongs_to :animal, :polymorphic => true
      t.belongs_to :tp
    end
  end
end