Rails Model has_many with multiple foreign_keys

Question

Relatively new to rails and trying to model a very simple family "tree" with a single Person model that has a name, gender, father_id and mother_id (2 parents). Below is basically what I want to do, but obviously I can't repeat the :children in a has_many (the first gets overwritten).

class Person < ActiveRecord::Base
  belongs_to :father, :class_name => 'Person'
  belongs_to :mother, :class_name => 'Person'
  has_many :children, :class_name => 'Person', :foreign_key => 'mother_id'
  has_many :children, :class_name => 'Person', :foreign_key => 'father_id'
end

Is there a simple way to use has_many with 2 foreign keys, or maybe change the foreign key based on the object's gender? Or is there another/better way altogether?

Thanks!

Burdine

For Rails 3, scope chainning, ActiveRecord::Relation and eventually has_many: #17477021 – Sylvia

You are looking for "composit keys": #17882605 – Profess

Answer 1

Found a simple answer on IRC that seems to work (thanks to Radar):

class Person < ActiveRecord::Base
  belongs_to :father, :class_name => 'Person'
  belongs_to :mother, :class_name => 'Person'
  has_many :children_of_father, :class_name => 'Person', :foreign_key => 'father_id'
  has_many :children_of_mother, :class_name => 'Person', :foreign_key => 'mother_id'
  def children
     children_of_mother + children_of_father
  end
end

Burdine

If there are any default scopes, in particular, those which might affect the order of the results, this solution will not work as the results will not be ordered as expected. – Assignable

you can get an ActiveRecord:Relation by defining #children like I did in my answer below – Galatea

Wouldn't this fire off two SQL queries? If you wanted to add more relations, this could get pretty inefficient. – Conduct

WARNING: With Rails 4.2 (and probably everything before this), calling children triggers two SQL requests (one for children_of_father and one for children_of_mother). With large a large data set, that won't be efficient. @stefvhuynh, thank you for raising the point. – Scholasticate

Answer 2

To improve on Kenzie's answer, you can achieve an ActiveRecord Relation by defining Person#children like:

def children
   children_of_mother.merge(children_of_father)
end

see this answer for more details

Galatea

WARNING: As explained in the answer you mention, relations are merged with an AND. Which doesn't work with this example, because it means you select only people who have mother_id and (instead of or) father_id set to the id of the target. I'm not a doctor but it shouldn't happen that often :) – Scholasticate

.merge worked in my case where I needed AND. Thank you! – Nepotism

Answer 3

Used named_scopes over the Person model do this:

class Person < ActiveRecord::Base

    def children
      Person.with_parent(id)
    end

    named_scope :with_parent, lambda{ |pid| 

       { :conditions=>["father_id = ? or mother_id=?", pid, pid]}
    }
 end

Teide

Answer 4

I believe you can achieve the relationships you want using :has_one.

class Person < ActiveRecord::Base
  has_one :father, :class_name => 'Person', :foreign_key => 'father_id'
  has_one :mother, :class_name => 'Person', :foreign_key => 'mother_id'
  has_many :children, :class_name => 'Person'
end

I'll confirm and edit this answer after work ; )

Nomanomad

This didn't work for me... seemed to good to be true, but I got the expected erorr for the has_many relationship: 'no column named person_id in table people. – Screak

Answer 5

My answer to Associations and (multiple) foreign keys in rails (3.2) : how to describe them in the model, and write up migrations is just for you!

As for your code,here are my modifications

class Person < ActiveRecord::Base
  belongs_to :father, :class_name => 'Person'
  belongs_to :mother, :class_name => 'Person'
  has_many :children, ->(person) { unscope(where: :person_id).where("father_id = ? OR mother_id = ?", person.id, person.id) }, class_name: 'Person'
end

So any questions?

Brelje

This is awesome! There are several incorrect answers to the question on StackOverflow, but this works perfectly. Quick correction though: you misspelled mother_id. – Degradation

@KalleSamuelsson thanks for you support! Love your comment! – Brelje

Answer 6

I prefer to use scopes for this issue. Like this:

class Person < ActiveRecord::Base
  belongs_to :father, :class_name => 'Person'
  belongs_to :mother, :class_name => 'Person'
  has_many :children_of_father, :class_name => 'Person', :foreign_key => 'father_id'
  has_many :children_of_mother, :class_name => 'Person', :foreign_key => 'mother_id'

  scope :children_for, lambda {|father_id, mother_id| where('father_id = ? AND mother_id = ?', father_id, mother_id) }
end

This trick make it easy to get children without use instances:

Person.children_for father_id, mother_id

Babbage

Answer 7

Not a solution to the general question as stated ("has_many with multiple foreign keys"), but, given a person can either be a mother or a father, but not both, I would add a gender column and go with

  has_many :children_of_father, :class_name => 'Person', :foreign_key => 'father_id'
  has_many :children_of_mother, :class_name => 'Person', :foreign_key => 'mother_id'
  def children
    gender == "male" ? children_of_father : children_of_mother
  end

Lewellen

Answer 8

I was looking for the same feature, if you don't want to return an array but a ActiveRecord::AssociationRelation, you can use << instead of +. (See the ActiveRecord documentation)

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

  has_many :children_of_father, :class_name => 'Person', :foreign_key => 'father_id'
  has_many :children_of_mother, :class_name => 'Person', :foreign_key => 'mother_id'

  def children
     children_of_mother << children_of_father
  end
end

Craniometer

The shovel operator (<<) would actually update database records, not just cause a union in the result set. Children of the father would now also be children of the mother. – Ventriculus