Double foreign key as a composite primary key

Questions / Help
,
1

Good morning,
I’m developing my first big project in elixir (my big first project at all) and i am having troubles dealing with
ecto associations.
I my relational model, I have two tables called players and alliances which have some attributes and a
player_id and a alliance_id as a primary keys respectively. Theese primary keys are not generated by me and they are strings.
I have a third table called alliances_players which is going to store the historical relationship between alliances and players. It has a composite primary key composed by player_id, alliance_id and the date the relation starts.

The migrations are defined like this:

defmodule TDB.Repo.Migrations.CreatePlayers do
  use Ecto.Migration

  def change do
    create table(:players, primary_key: false) do
      add :player_id, :string, primary_key: true
      add :name, :string

      timestamps()
    end
  end
end


defmodule TDB.Repo.Migrations.CreateAlliances do
  use Ecto.Migration

  def change do
    create table(:alliances, primary_key: false) do
      add :alliance_id, :string, primary_key: true
      add :name, :string

      timestamps()
    end
  end
end


defmodule TDB.Repo.Migrations.CreateAlliancesPlayers do
  use Ecto.Migration

  def change do
    create table(:alliances_players, primary_key: false) do
      add :player_id, references(:players, name: :player_id, column: :player_id, type: :string), primary_key: true
      add :alliance_id, references(:alliances, name: :alliance_id, column: :alliance_id, type: :string), primary_key: true
      add :start_date, :date, primary_key: true
    end
  end
end

And the schemas are like this:

defmodule TDB.Player do
  use Ecto.Schema
  @primary_key {:player_id, :string, []}

  @type t :: %__MODULE__{
    player_id: String.t(),
    name:  String.t(),
    inserted_at: NaiveDateTime.t(),
    updated_at: NaiveDateTime.t()
  }


  schema "players" do
    field :name, :string
    
    has_many :alliances_players, TDB.Alliance_Player, foreign_key: :player_id
    timestamps()
  end
end

defmodule TDB.Alliance do
  use Ecto.Schema

  @primary_key {:alliance_id, :string, []}

  @type t :: %__MODULE__{
    alliance_id: String.t(),
    name:  String.t(),
    inserted_at: NaiveDateTime.t(),
    updated_at: NaiveDateTime.t()
  }

  schema "alliances" do
    field :name, :string
    
    has_many :alliances_players, TDB.Alliance_Player, foreign_key: :alliance_id
    timestamps()
  end
end


defmodule TDB.Alliance_Player do
  use Ecto.Schema

  schema "alliances_players" do
    field :start_date, :date, primary_key: true
    belongs_to :player_id, TDB.Player, primary_key: true, references: :player_id
    belongs_to :alliance_id, TDB.Alliance, primary_key: true, references: :alliance_id
  end
end

The tables are well created and everything looks fine but when I try to create and alliance_player struct, it looks like this:

%TDB.Alliance_Player{
  __meta__: #Ecto.Schema.Metadata<:built, "alliances_players">,
  alliance_id: #Ecto.Association.NotLoaded<association :alliance_id is not loaded>,
  alliance_id_id: nil,
  id: nil,
  player_id: #Ecto.Association.NotLoaded<association :player_id is not loaded>,
  player_id_id: nil,
  start_date: nil
}

I have spent many hours trying to understanding but the multiple id fields are killing me.
Sorry for the long question, if anyone offers a solution i will be very glad with them:).
Thanks.

2 
defmodule TDB.Alliance_Player do
  use Ecto.Schema

  schema "alliances_players" do
    field :start_date, :date, primary_key: true
-    belongs_to :player_id, TDB.Player, primary_key: true, references: :player_id
+    belongs_to :player, TDB.Player, primary_key: true, references: :player_id
-    belongs_to :alliance_id, TDB.Alliance, primary_key: true, references: :alliance_id
+    belongs_to :alliance, TDB.Alliance, primary_key: true, references: :alliance_id
  end
end

belongs_to/3 docs:

In fact, when you invoke this macro, a field with the name of foreign key is automatically defined in the schema for you.

Options

  • :foreign_key - Sets the foreign key field name, defaults to the name of the association suffixed by _id. For example, belongs_to :company will define foreign key of :company_id. The associated has_one or has_many field in the other schema should also have its :foreign_key option set with the same value.
1 Like
3

Looks better but the problem of duplicated id fields and the id field is still here:

%TDB.Alliance_Player{
  __meta__: #Ecto.Schema.Metadata<:built, "alliances_players">,
  alliance: #Ecto.Association.NotLoaded<association :alliance is not loaded>,
  alliance_id: nil,
  id: nil,
  player: #Ecto.Association.NotLoaded<association :player is not loaded>,
  player_id: nil,
  start_date: nil
}

Thanks for the reply

4

Add @primary_key false above ` the schema definition:

+ @primary_key false
  schema "alliances_players" do
    field :start_date, :date, primary_key: true
    belongs_to :player, TDB.Player, primary_key: true, references: :player_id
    belongs_to :alliance, TDB.Alliance, primary_key: true, references: :alliance_id
  end
1 Like
5

Thanks:)