Ecto Join Table Validation

Question

I have this join table called: seasons_teams

  schema "seasons_teams" do
    field(:score, :integer)
    belongs_to(:season, Season, foreign_key: :season_id, type: :binary_id)
    belongs_to(:team, Team, foreign_key: :team_id, type: :binary_id)

    timestamps()
  end

This is great but I want to validate that duplicate of season and team can't exist.

For example:

  %MyApp.SeasonsTeams{
    __meta__: #Ecto.Schema.Metadata<:loaded, "seasons_teams">,
    id: "89ec0dbd-cc61-4ba9-8dcf-b86b858d2882",
    inserted_at: ~N[2019-11-10 00:58:27],
    score: nil,
    season: #Ecto.Association.NotLoaded<association :season is not loaded>,
    season_id: "79c4bd04-d362-495c-ab13-649b299c23e2",
    team: #Ecto.Association.NotLoaded<association :team is not loaded>,
    team_id: "b6c408ef-d48e-4de9-8a46-8ae7bb12b699",
    updated_at: ~N[2019-11-10 00:58:27]
  },
  %MyApp.SeasonsTeams{
    __meta__: #Ecto.Schema.Metadata<:loaded, "seasons_teams">,
    id: "89a7fc61-6ce4-4a6d-a9dc-8e3494378e33",
    inserted_at: ~N[2019-11-10 00:58:37],
    score: nil,
    season: #Ecto.Association.NotLoaded<association :season is not loaded>,
    season_id: "79c4bd04-d362-495c-ab13-649b299c23e2",
    team: #Ecto.Association.NotLoaded<association :team is not loaded>,
    team_id: "b6c408ef-d48e-4de9-8a46-8ae7bb12b699",
    updated_at: ~N[2019-11-10 00:58:37]
  }

You can see that both of these records have the same season_id and the same team_id I don't want this to be possible. I'm curious if Phoenix/Elixir/Ecto provides a nice way to handle this case? Thank you!

zwippie zwippie · Accepted Answer · 2019-11-10T15:24:41

Create a unique index on that table that spans the two fields and use unique_constraint to validate the unique constraint:

# In the migration
create unique_index(:seasons_teams, [:season_id, :team_id], name: :seasons_teams_season_id_team_id_index)

# In the changeset function
cast(season_team, params, [:season_id])
|> unique_constraint(:season_id, name: :seasons_teams_season_id_team_id_index)

Ecto Join Table Validation

1 Answers