ActiveRecord has_many where two columns in table A are primary keys in table B

Question

I have a model, Couple, which has two columns, first_person_id and second_person_id and another model, Person, whose primary key is person_id and has the column name

Here's the usage I want:

#including 'Person' model for eager loading, this is crucial for me
c = Couple.find(:all, :include => :persons)[0]
puts "#{c.first_person.name} and #{c.second_person.name}"

So how can I do this?

If you're using Rails, why would the primary key on the Person model be "person_id" and not just "id"? — tfwright, Jan 24 '10 at 02:44
I changed the names of my models for this post as I'd like to keep the names off the internet. It should be `id` but that's not a difficult fix. Just use `set_primary_key`. — user94154, Jan 24 '10 at 03:21

score 14 · Accepted Answer · answered Jan 25 '10 at 22:56

The relationships declared in Couple should look like this:

class Couple
  named_scope :with_people, { :include => [:first_person, :second_person] }
  belongs_to :first_person, :class_name => 'Person'
  belongs_to :second_person, :class_name => 'Person'
end

#usage:
Couple.with_people.first
# => <Couple ... @first_person: <Person ...>, @second_person: <Person ...>>

Those in Person depend on whether a Person can be part of more than one Couple. If a Person can only belong to one Couple and can't be the "first" Person on one and the Second on another, you might want:

class Person
  has_one :couple_as_first_person, :foreign_key => 'first_person_id', :class_name => 'Couple'
  has_one :couple_as_second_person, :foreign_key => 'second_person_id', :class_name => 'Couple'

  def couple
    couple_as_first_person || couple_as_second_person
  end
end

If a Person can belong to several Couples, and there's no way to tell whether they're the "first" or "second" in any given Couple, you might want:

class Person
  has_many :couples_as_first_person, :foreign_key => 'first_person_id', :class_name => 'Couple'
  has_many :couples_as_second_person, :foreign_key => 'second_person_id', :class_name => 'Couple'

  def couples
    couples_as_first_person + couples_as_second_person
  end
end

This is a well-written, well thought-out answer. Thanks so much. I'll test this soon and let you know. — user94154, Jan 26 '10 at 01:54
I just did something like this using AREL: `def widgets; Widget.where( Wiget.arel_table[:asset1_id].eq(id).or( Widget.arel_table[:asset2_id].eq(id) ) ); end` — thekingoftruth, Mar 07 '15 at 01:22

score 0 · Answer 2 · answered Jan 24 '10 at 01:07

0

Untested, but according to the Rails API documentation, maybe something like:

class Couple < ActiveRecord::Base
    has_one :person, :foreign_key => :first_person_id
    has_one :person, :foreign_key => :second_person_id
end

answered Jan 24 '10 at 01:07

clee

10,943
6
36
28

Those should be belongs_to, not has_one, since the foreign keys are on this model. – tfwright Jan 24 '10 at 02:45

score 0 · Answer 3 · answered Jan 24 '10 at 03:37

0

Theory only, untested:

Create two subclasses of Person:

class FirstPerson < Person
   belongs_to :couple

class SecondPerson < Person
   belongs_to :couple

Couple class has_many of each:

class Couple
   has_many :first_persons, :foreign_key => :first_person_id
   has_many :second_persons, :foreign_key => :second_person_id

Then find:

 Couple.all(:include => [:first_persons, :second_persons])

answered Jan 24 '10 at 03:37

insane.dreamer

2,052
1
17
21

shouldn't a couple `has_one :first_person` and `:second_person`? – user94154 Jan 24 '10 at 19:46

ActiveRecord has_many where two columns in table A are primary keys in table B

3 Answers3

Linked