Associations with 2 foreign keys

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!

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)".

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

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.

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

HTH,

Peter

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