Associations with 2 foreign keys

Rails
#1

Hola,

Just wondering if rails will allow me to have one model belong to
another model via 2 foreign keys in the same table, not just one?

So like this…

Class Pair
belongs_to :individuals, :foreign_key => “individual_a”
belongs_to :individuals, :foreign_key => “individual_b”
end

Class Individual
has_many :pairs
end

my database table “pairs” has the following columns:
id
individual_a
individual_b

I want to be able to call ALL pairs that belong to an individual, who
could be listed as either “individual_a” or “individual_b”

So like

If my pair table had the following rows…
id | individual_a | individual_b
1 | bob | frank
2 | frank | dave

So we see that frank has 2 pairs, but is listed as individual_a in one,
and individual_b in the other.

I want to do something like

Individual.find(franks_id).pairs.all = [PairObject_1, PairObject_2]

… so that it returns every pair that frank is a part of, regardless of
if he is individual_a or individual_b

I hope that makes sense.

Cheers!

#2

adam witt wrote in post #1048895:

Class Pair
belongs_to :individuals, :foreign_key => “individual_a”
belongs_to :individuals, :foreign_key => “individual_b”
end

Class Individual
has_many :pairs
end

This is a classic “repeating groups” database design problem. I don’t
really feel like expending brain cells to explain it. However, as a hint
look up “database normalization” paying close attention to the “First
Normal Form (1NF)”.

#3

On Sun, Feb 26, 2012 at 9:22 AM, adam witt [email protected] wrote:

end

That would be something like:

Class Pair
belongs_to :individual_a, :class_name => “Individual”
belongs_to :individual_b, :class_name => “Individual”
end

Note that the first argument to belongs_to is the name of the
association.
In the most default case, that is also the name of the table, but that
is
just
a simplified convention, you could make 10 associations with different
names to the same table. These association names have to be different
of course to be effectively 2 different associations.

Class Individual
has_many :pairs
end

You might need a “custom SQL” definition here to find the pairs where
either individual_a_id or individual_b_id matches self.id (self is the
individual here) (read up on the “:finder_sql” option, there is a chance
the JOIN feature is much more performant than “id = a OR id = b”).

Alternatively (slightly less efficient, but still acceptable), you could
make
2 queries and join the data in Ruby.

Class Individual
has_many :pairs_a, :class_name => “Pair”, :foreign_key =>
“individual_a_id”
has_many :pairs_b, :class_name => “Pair”, :foreign_key =>
“individual_b_id”

def pairs
pairs_a + pairs_b
end
end

None of this code will not protect you from double pairs (that is two or
more
“pairs” entries that somehow logically infer the same pair).

my database table “pairs” has the following columns:
id
individual_a
individual_b

Suggest to change column names to “individual_a_id” and
“individual_b_id”
to stay closer to the Rails idioms.

So we see that frank has 2 pairs, but is listed as individual_a in one,
and individual_b in the other.

I want to do something like

Individual.find(franks_id).pairs.all = [PairObject_1, PairObject_2]

… so that it returns every pair that frank is a part of, regardless of
if he is individual_a or individual_b

HTH,

Peter

#4

Cheers Pete, I ended up using your solution. Cheers everyone.