I need a new type of polymorphic has_many association with dynamic foreign_key

A vanilla polymorphic belongs_to association allows different
association instances to have targets in different classes. I want to
do a has_many association where different association instances have
different foreign_keys. Is such a thing possible?

My schema looks like

  create_table "people", :force => true do |t|
    t.string "name"
    t.string "sex"
    t.integer "father_id"
    t.integer "mother_id"
  end

and my model I want something like:

class Person < ActiveRecord::Base
  belongs_to :father, :class_name => "Person"
  belongs_to :mother, :class_name => "Person"

  #I want something like this is invalid syntax
  has_many :children, :class_name => "Person", :foreign_key => ( sex
== 'M' ? :father_id : :mother_id )
end

Will it be hard to create an association that can handle this?

Thanks in advance for any advice you can give me,
Joe

What about children with two mums or two dads? Or the more frequent
occasion where there's a mum, dad and one or more step-parents? How
about children looked after by their grandparents?

You might be better off (and it would make your example query easier
too) if you removed the foreign keys from people, and had a
relationship like:

class Person < ActiveRecord::Base
has_many :parents
end

create_table "parents" do |t|
t.string "relationship" # mother/father/stepmother/etc
t.integer "child_id"
t.integer "person_id"
end

class Parent < ActiveRecord::Base
belongs_to :child, :class_name => "Person"
belongs_to :person # this is the link for the "parent" person
end

If you *have* to only have one mother and father and nothing else, my
suggestion lets you do that by putting an index on parents across
relationship and child_id to ensure only one of each, and validate
that only mother/father is put into the relationship field.

Then, including a relationship like:
has_many :children, :through => "parents", :class_name => "Person"

... gives you Person.first.children

PS Here's a couple of links to DB designs for family relationships:
http://www.familyhistorysa.info/dbDesign.html
http://www.databaseanswers.org/data_models/genealogy/index.htm

And here's one for modelling marriages (not strictly what you're
talking about, but the principle of husband/wife not being as simple
as first thought is similar to your issue with mother/father):
http://qntm.org/gay

Joe wrote:

A vanilla polymorphic belongs_to association allows different
association instances to have targets in different classes. I want to
do a has_many association where different association instances have
different foreign_keys. Is such a thing possible?

My schema looks like

  create_table "people", :force => true do |t|
    t.string "name"
    t.string "sex"
    t.integer "father_id"
    t.integer "mother_id"
  end

I believe that you need a generic relationship model... the vagaries of
people's interactions/behaviors/affairs (ha!) leads to relationship
instances like:

person_1 person_2 relationship (person_1 is the what of person_2)
-------- -------- ------------
Person A Person B spouse
Person A Person C ex-spouse
Person D Person A child
Person D Person B stepchild
Person D Person C child
etc, etc, etc.

More work up front, but eminently more flexible.

I think you can accomplish what you need with :has many :through.
You'll need another table to store the relationships as Ar Chron
suggested.

Hi Michael-

Thanks so much for the reply. Those links were very helpful, and I
especially enjoyed the one about gay marriage.

So it seems like it would be very difficult to do what I originally
wanted (create a new kind of association that works in a new way), and
it would require an understanding of rails internals and a lot of
work. The upshot is that if I think I need to change rails, I'm
probably wrong, and I just need to change my schema to conform to best
practices.

So I will take the advice, and alter the schema to fit with rails
better. And following tips I got from those articles, it will have
more flexibility too, though I think it will also have much more
complexity than I had originally expected. I'm still nailing down my
perfect schema, but I think I'm getting close.

Thanks for your advice
Joe

I found a way to make my original schema work. Single table
inheritance. Define the associations on the subclasses, and you can
use a different foreign key in each subclass. Works like a charm.

I'm still debating with myself about a more sophisticated schema like
the ones suggested though.