Issues with unique_constraint and many to many

azrael

Hi,

I am working on a first elixir project and I am struggling quite a lot with the many_to_many association. I have got it sort-of working using the auto-generated link table, however it does not seem to be checking for uniqueness on the link succesfully.

defmodule KevinCadleFantasyGame.Lineup do
  use Ecto.Schema
  import Ecto.Changeset

  alias KevinCadleFantasyGame.{Player, Repo, Lineup}

  schema "lineups" do
    many_to_many :players, Player,
      join_through: "lineup_players", unique: true

    timestamps()
  end

  @doc false
  def changeset(lineup, attrs) do
    player_ids = if Map.has_key?(attrs, :player_ids), do: attrs.player_ids, else: []
    players = Player.by_ids(player_ids)

    lineup
    |> Repo.preload(:players)
    |> cast(attrs, [:id])
    |> put_assoc(:players, players)
    |> unique_constraint(:players)
    |> validate_required([])
  end


  def create_or_update(params) do
    cs = changeset(%Lineup{}, params)

    if cs.valid? do
      Repo.insert!(cs,
        on_conflict: :nothing,
        conflict_target: :id
        )
    else
      cs
    end
  end
end

and the migration:

defmodule KevinCadleFantasyGame.Repo.Migrations.CreateLineups do
  use Ecto.Migration

  def change do
    create table(:lineups) do

      timestamps()
    end


    create table("lineup_players", primary_key: false) do
      add :player_id, references(:players)
      add :lineup_id, references(:lineups)
    end

    create unique_index(:lineup_players, [:player_id, :lineup_id])
  end
end

With a non-unique link it is giving me a postgres error due to a failure of uniqueness constraint, which is fine, but ideally I’d probably like it to be caught on the changset. What am i doing wrong?

Thanks,

2 comments

#ecto #changesets #many-to-many-association

1 996 2

2022-08-30 12:31:48 UTC

Showing Posts 1 to 2

Show Best Posts
Show All (oldest first)
Show All (newest first)

kokolegorille

Hello and welcome, You might give a name to your constraint…

create unique_index(:lineup_players, [:player_id, :lineup_id], name: :my_constraint)

and use it like this

|> unique_constraint(:players, name: :my_constraint)

Post #1

azrael

I had tried that, it doesn’t seem to make any difference. With renamed to unique_lineup_players (rather than the previous default of lineup_players_player_id_lineup_id_index), i still get the postgrex error:

** (Postgrex.Error) ERROR 23505 (unique_violation) duplicate key value violates unique constraint "unique_lineup_players"

    table: lineup_players
    constraint: unique_lineup_players

Key (player_id, lineup_id)=(309, 10) already exists.
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:822: Ecto.Adapters.SQL.insert_all/9
    (ecto 3.8.4) lib/ecto/repo/schema.ex:58: Ecto.Repo.Schema.do_insert_all/7
    (ecto 3.8.4) lib/ecto/association.ex:1330: Ecto.Association.ManyToMany.on_repo_change/5
    (ecto 3.8.4) lib/ecto/association.ex:572: anonymous fn/8 in Ecto.Association.on_repo_change/7
    (elixir 1.13.4) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.8.4) lib/ecto/association.ex:568: Ecto.Association.on_repo_change/7
    (elixir 1.13.4) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.8.4) lib/ecto/association.ex:532: Ecto.Association.on_repo_change/4
    (ecto 3.8.4) lib/ecto/repo/schema.ex:905: Ecto.Repo.Schema.process_children/5
    (ecto 3.8.4) lib/ecto/repo/schema.ex:985: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:1222: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.4.2) lib/db_connection.ex:1562: DBConnection.run_transaction/4
    (ecto 3.8.4) lib/ecto/repo/schema.ex:269: Ecto.Repo.Schema.insert!/4

I can also see that it doesn’t query the link table at all, so its not clear how it ever would work:

[debug] QUERY OK source="players" db=0.0ms idle=1766.0ms
SELECT p0."id", p0."name", p0."headshot_url", p0."team_short_name", p0."position", p0."inserted_at", p0."updated_at" FROM "players" AS p0 WHERE (p0."id" = ANY($1)) [[309]]←[90m
Γå│ KevinCadleFantasyGame.Lineup.changeset/2, at: lib/kevin_cadle_fantasy_game/lineup.ex:17
[debug] QUERY OK db=0.0ms idle=1766.0ms
begin []←[90m
Γå│ :erl_eval.do_apply/6, at: erl_eval.erl:680
[debug] QUERY OK db=0.0ms
INSERT INTO "lineups" ("id","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT ("id") DO NOTHING [10, ~N[2022-08-30 12:28:28], ~N[2022-08-30 12:28:28]]←[90m
Γå│ :erl_eval.do_apply/6, at: erl_eval.erl:680
[debug] QUERY ERROR db=0.0ms
INSERT INTO "lineup_players" ("lineup_id","player_id") VALUES ($1,$2) [10, 309]←[90m
Γå│ :erl_eval.do_apply/6, at: erl_eval.erl:680
[debug] QUERY OK db=0.0ms
rollback []←[90m
Γå│ :erl_eval.do_apply/6, at: erl_eval.erl:680

Post #2

— All posts loaded —