Multiple foreign keys

Rails
1

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.

2

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.

:confused:

3

On 5 April 2010 08:31, Josh C. [email protected] 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

4

On Mon, Apr 5, 2010 at 3:15 AM, Colin L. [email protected]
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}
)”

5

On 5 April 2010 09:58, Josh C. [email protected] 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