multiple foreign keys

rubyonrails-talk
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

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.

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

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

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

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