Creating multiple join tables using the same 2 tables

[seatuna]

I currently have 2 tables with a many-to-many relationship: Players, and Characters. I've created a join table "play_as_character" (for the characters that a player uses / plays as) which allows me to use player.characters or character.players.

But I also want another join table "play_against_character" (characters that the player wants to play against) that uses the same 2 tables (players and characters). Is this possible? If I create another join table with a different name, it would just conflict since the many-to-many relationship, player.characters already exists?

Or should I go back to my original idea of storing character information in player (so the Player model will have name:string, rank:string, characters:array, and characters they want to play against:array) instead of separating out the characters in a separate table?

[faetea]

I have you drawn it out yet? Post your diagrams.

Is this game Player-verses-Player -or- Player-verses-Computer? If Sally want to play as Mario, and fight computer as Samus, then Sally has the opponent of Samus through the Character of Mario.

[gaand]

If you actually need two join tables, and I don't think I have enough information to know, you need to name the relationships differently in the has_many.

So, something like:

has_many :characters, through: :play_as_character
has_many :opponents, through: :play_against_character, class_name: 'Character'

[seatuna]

I'm creating an app that will generate match-ups for the Street Fighter 5 game, based on a player's characters, and the characters they want to play against (opponents is a great word! I couldn't think of it earlier!)

Player has name rank characters that they use - join table characters they want to fight against (desired opponents) - join table

My schema for Player looks like this:

  create_table "players", force: :cascade do |t|
    t.string   "name"
    t.string   "rank"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

There are only 15 characters they can choose from. My characters table only has a name attribute.

create_table "characters", force: :cascade do |t|
    t.string   "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

I talked to Matt about the relationships on Friday, and he suggested creating a join table for Players and Characters. I realized today that I would need to create 2 join tables since players will supply information about characters they use, and desired opponents (but drawing from the same pool of characters), since there are different meanings

I think Antony's suggestion is what I'm going for, I'll try it out and close the issue if it works. I've attached my user stories and ERD from Friday (not updated to include the join tables though) but it should illustrate what I'm trying to achieve. Let me know if maybe I don't need two join tables now that I've given you more info!

vmatch-user-story.docx

[seatuna]

It worked!

I can now enter Player.opponents, and it uses the same Characters table as Player.characters :)

But instead of class_name: 'Character', rails spat out an error that told me to try source: :characters instead. It worked!

class Player < ActiveRecord::Base
  has_many :characters, through: :play_as_characters
  has_many :play_as_characters
  has_many :opponents, through: :play_against_characters, source: :character
  has_many :play_against_characters
end

[jrhorn424]

This is a neat modeling problem.