# Multiple foreign keys

**URL:** <https://www.ruby-forum.com/t/multiple-foreign-keys/186335>\
**Category:** Rails\
**Created:** [April 5, 2010, 9:33am UTC](https://www.ruby-forum.com/t/multiple-foreign-keys/186335 "2010-04-05T09:33:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jcain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/3d9bf3/32.png) [@jcain](https://www.ruby-forum.com/u/jcain)\
**Post date:** [April 5, 2010, 9:33am UTC](https://www.ruby-forum.com/t/multiple-foreign-keys/186335/1 "2010-04-05T09:33:47Z")

</div>

Hi, I have two players playing a game.  
The game has foreign keys into each player: player1\_id, player2\_id

From the Player model, I can get the opponent self played if I know  
which  
player self was, but I want to agnostically pull everyone that self has  
played against, regardless of whether self was player1 or player2

I can get it with find\_by\_sql, but then my named scopes don’t work.

Really struggling to figure this out, looked at the guides, the api,  
googled, and played around, and I’m running out of time and steam.

Any ideas?

Thanks, Josh.

---

<div class="post-metadata">

**Author:** ![jcain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/3d9bf3/32.png) [@jcain](https://www.ruby-forum.com/u/jcain)\
**Post date:** [April 5, 2010, 9:38am UTC](https://www.ruby-forum.com/t/multiple-foreign-keys/186335/2 "2010-04-05T09:38:28Z")

</div>

Also having difficulty getting the has\_many to work with this. I thought  
about a separate join table for it,and even got it working with  
has\_and\_belongs\_to\_many :beat , :class\_name =\> ‘Player’ , :foreign\_key  
=\>  
‘winner\_id’ , :association\_foreign\_key =\> ‘loser\_id’ , :join\_table =\>  
‘games’ , :conditions =\> { :active =\> true }

has\_and\_belongs\_to\_many :lost\_to , :class\_name =\> ‘Player’ ,  
:foreign\_key =\>  
‘loser\_id’ , :association\_foreign\_key =\> ‘winner\_id’ , :join\_table =\>  
‘games’ , :conditions =\> { :active =\> true }

But then I still had the same problem of selecting everyone played and  
wasn’t sure how to deal with draws, and the game doesn’t know who is  
player1  
and who is player2, so I moved them into the game table.

😕

---

<div class="post-metadata">

**Author:** ![jcain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/3d9bf3/32.png) [@jcain](https://www.ruby-forum.com/u/jcain)\
**Post date:** [April 5, 2010, 10:17am UTC](https://www.ruby-forum.com/t/multiple-foreign-keys/186335/3 "2010-04-05T10:17:41Z")

</div>

On 5 April 2010 08:31, Josh C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> googled, and played around, and I’m running out of time and steam.  
> It would be less effort for us to work out what you are trying to do  
> if you gave us the model relationships (belongs\_to etc). Also if you  
> show the sql that works it would allow us to see what you mean.

Colin

---

<div class="post-metadata">

**Author:** ![jcain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/3d9bf3/32.png) [@jcain](https://www.ruby-forum.com/u/jcain)\
**Post date:** [April 5, 2010, 11:00am UTC](https://www.ruby-forum.com/t/multiple-foreign-keys/186335/4 "2010-04-05T11:00:24Z")

</div>

On Mon, Apr 5, 2010 at 3:15 AM, Colin L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> It would be less effort for us to work out what you are trying to do  
> if you gave us the model relationships (belongs\_to etc). Also if you  
> show the sql that works it would allow us to see what you mean.
> 
> Colin

Basically I want to say

Game \< AR::Base  
belongs\_to :player1  
belongs\_to :player2

Player \< AR::Base  
has\_and\_belongs\_to\_many :players , :through =\> :games  
end

And I want the current player to not care if it is player1 or player2

Here is the find\_by\_sql:

Player.find\_by\_sql “select \* from players p where p.id in ( select  
distinct  
player1\_id from games g where g.player2\_id = #{self.id} ) or p.id in (  
select distinct player2\_id from games g where g.player1\_id = #{self.id}  
)”

---

<div class="post-metadata">

**Author:** ![jcain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/3d9bf3/32.png) [@jcain](https://www.ruby-forum.com/u/jcain)\
**Post date:** [April 5, 2010, 11:49am UTC](https://www.ruby-forum.com/t/multiple-foreign-keys/186335/5 "2010-04-05T11:49:34Z")

</div>

On 5 April 2010 09:58, Josh C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Game \< AR::Base  
> belongs\_to :player1  
> belongs\_to :player2

This will have to be something like  
belongs\_to :player1, :class\_name =\> ‘Player’, foreign\_key =\> player1\_id  
belongs\_to :player2, :class\_name =\> ‘Player’, foreign\_key =\> player2\_id

> Player \< AR::Base  
> has\_and\_belongs\_to\_many :players , :through =\> :games

something like  
has\_many :game\_where\_i\_am\_player1s, :class\_name =\> ‘Game’,  
:foreign\_key =\> player1\_id  
has\_many :game\_where\_i\_am\_player2s, :class\_name =\> 'Game, :foreign\_key  
=\> player2\_id

has\_many :player2s, :through =\> :game\_where\_i\_am\_player1s  
has\_many :player1s, :through =\> :game\_where\_i\_am\_player2s

Then if you have @player you can say  
@player.player2s + @player.player1s  
to get all the opponents

I am sure there are better ways but that may get you going. All  
untested of course and quite possibly a load of rubbish.

Colin