How to correctly configure the connection 1 to many, when we refer to the same field 2 times

Questions / Help
1

There is a user table with the name and game fields with the player1, player2 fields. How to relate this?

i did so:
-----model users-------------

schema "users" do
    field :name, :string
    
    has_many :games, Game
  end

------model games---------

chema "games" do
    belongs_to :user1, User, foreign_key: :player1
    belongs_to :user2, User, foreign_key: :player2
  end

def changeset(game, attrs) do
    game
    |> put_assoc(:user1, attrs["player1"], required: true)
    |> put_assoc(:user2, attrs["player2"], required: true)
  end

---------migration users------------

def change do
    create table(:users) do
      add :name, :string
    
    end
  end

------migration games---------------

def change do
    create table(:games) do
      add :player1, references(:users, on_delete: :delete_all)
      add :player2, references(:users, on_delete: :delete_all)
    end
  end
2

Hello and welcome to the forum,

You should probably set in your User schema

    has_many :games1, Game, foreign_key: :player1
    has_many :games2, Game, foreign_key: :player2

Then, to get games for a user You could have a query like this

  def list_user_games_query(%User{id: user_id}, args) do
    from(
      g in list_games_query(args),
      where: g.player1 == ^user_id or g.player2 == ^user_id
    )
  end

PS: I omitted list_games_query() in the code, but it’s just a query returning games

2 Likes
3

Hello,
Thanks for answer.
I do not understand why two foreign keys are required in the user table?

4

This does imply Game as a user_id, but it is not the case… You keys are player1 and player2

A user can play a game as player1, or player2

From docs

  • :foreign_key - Sets the foreign key, this should map to a field on the other schema, defaults to the underscored name of the current schema suffixed by _id
1 Like
5

Thanks)
How can I verify that I have created the correct link?
I use it

Map.put_new("player1", user1) |> Map.put_new("player2", user2) |> MyGame.Games.create_game()
game = MyGame.Games.get_game!(1)
MyGame.Repo.preload(game, [:user1, :user2])

In both cases I displayed the card the same way

6

I am sorry, I don’t understand.

7

How can I verify that the connections are correctly configured?

has_many :games, Game

and

has_many :games1, Game, foreign_key: :player1
has_many :games2, Game, foreign_key: :player2

in the preloader output the same result.
This function return some result too

def list_user_games_query(%User{id: user_id}) do    
    query = from g in Game,
      where: g.player1 == ^user_id or g.player2 == ^user_id,
      select: g
  end
8

Please show the output, and the command.

9

Thanks for helpful)
I did the preloader wrong
I did:

game = MyGame.Games.get_game!(1)
MyGame.Repo.preload(game, [:user1, :user2])

Correct:

user1 = MyGame.Users.get_user!(1)
MyGame.Repo.preload(user1, [:player1, :player2])