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!

For Rails 3, scope chainning, ActiveRecord::Relation and eventually `has_many`: http://stackoverflow.com/questions/17476521/rails-has-many-custom-activerecord-association/17476639#17476639 — MrYoshiji, Jul 04 '13 at 19:02
You are looking for "composit keys": http://stackoverflow.com/questions/17882105/is-it-possible-to-define-composite-primary-key-for-table-using-active-record — xpepermint, Feb 27 '14 at 12:53

score 46 · Accepted Answer · answered Nov 21 '08 at 03:22

46

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

answered Nov 21 '08 at 03:22

Kenzie

1,285
2
11
13

1

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. – mgadda Aug 09 '12 at 17:54
you can get an `ActiveRecord:Relation` by defining #children like I did in my answer below – stevenspiel Aug 16 '14 at 18:11
2

Wouldn't this fire off two SQL queries? If you wanted to add more relations, this could get pretty inefficient. – stefvhuynh Nov 22 '14 at 22:37
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. – philippe_b Feb 17 '16 at 15:57

score 17 · Answer 2 · edited May 23 '17 at 12:03

17

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

edited May 23 '17 at 12:03

Community

1
1

answered Aug 16 '14 at 18:10

stevenspiel

5,775
13
60
89

2

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 :) – philippe_b Feb 17 '16 at 16:02
.merge worked in my case where I needed AND. Thank you! – Vlad May 04 '16 at 10:47

score 9 · Answer 3 · answered Mar 27 '10 at 14:13

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

score 6 · Answer 4 · answered Nov 21 '08 at 02:44

6

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 ; )

answered Nov 21 '08 at 02:44

Gordon Wilson

26,244
11
57
60

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`. – deivid Feb 21 '14 at 10:48

score 5 · Answer 5 · edited May 23 '17 at 11:33

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?

edited May 23 '17 at 11:33

Community

1
1

answered Nov 04 '16 at 16:56

sunsoft

476
6
10

This is awesome! There are several incorrect answers to the question on StackOverflow, but this works perfectly. Quick correction though: you misspelled `mother_id`. – KurtPreston Dec 13 '16 at 07:27
@KalleSamuelsson thanks for you support! Love your comment! – sunsoft Oct 19 '17 at 03:05

score 4 · Answer 6 · answered May 08 '13 at 20:33

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

score 3 · Answer 7 · answered Dec 12 '14 at 09:07

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

score 3 · Answer 8 · answered Jun 07 '16 at 20:05

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

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. — Jacob Vanus, May 18 '21 at 03:57

Rails Model has_many with multiple foreign_keys

8 Answers8

Linked