Many to Many and odd unique_constraint error

Questions / Help
,
1

TLDR:

_Don’t assume that passing ids will be enough for ecto to create the association. You have to preload structs, if you dont ecto will try to create new records via :on_replace with the id and you will get a unique: pkey error

I have user who I want to associate to many programs.
I have a program that can have many users.

from my edit for for a given user I want to be able to select multiple programs via a multi select.

So lets start with my migration

defmodule MyApp.Repo.Migrations.CreateJoinProgramsUsers do
  use Ecto.Migration

  def change do
    create table(:programs_users, primary_key: false) do
      add :program_id, references(:programs)
      add :user_id, references(:users)
    end
  end
end

I feel confident that the migration is correct but here it is for reference just incase.

Now on to the my user class.

defmodule MyApp.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset
  alias MyApp.Accounts.User

  schema "users" do
    ...

    many_to_many :programs, MyApp.Organization.Program, join_through: "programs_users"

    ...
  end

  @doc false
  def changeset(%User{} = user, attrs) do
    user
    |> MyApp.Repo.preload(:programs)
    |> cast(attrs, [...])
    |> put_assoc(:programs, pull_programs(attrs))
  end

  
  def pull_programs(attrs) do
    case attrs do
      %{"programs" => _} ->
        attrs["programs"]
        |> Enum.map fn(id) ->
          {id, ""} = Integer.parse(id)
          %{id: id }
        end
      _ ->
        []
    end
  end
end

I dont feel confident that I have the changeset setup correct. I could use a review on that.

Ok so heres where I’m having an issue.

iex(1)> user = Accounts.get_user!(3)

%MyApp.Accounts.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
 ...
 programs: [],
 ...
 }

iex(2)> Accounts.update_user(user, %{"programs" => ["1"]})

[debug] QUERY OK db=0.3ms
begin []
[debug] QUERY ERROR db=2.1ms
INSERT INTO "programs" ("id","inserted_at","updated_at") VALUES ($1,$2,$3) [1, {{2017, 9, 29}, {20, 42, 18, 606804}}, {{2017, 9, 29}, {20, 42, 18, 606812}}]
[debug] QUERY OK db=0.4ms
rollback []
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * unique: programs_pkey

If you would like to convert this constraint into an error, please
call unique_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

    (ecto) lib/ecto/repo/schema.ex:495: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir) lib/enum.ex:1255: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/schema.ex:481: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto) lib/ecto/repo/schema.ex:213: anonymous fn/13 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/association.ex:953: Ecto.Association.ManyToMany.on_repo_change/4
    (ecto) lib/ecto/association.ex:330: anonymous fn/7 in Ecto.Association.on_repo_change/6
    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/association.ex:327: Ecto.Association.on_repo_change/6
    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/association.ex:293: Ecto.Association.on_repo_change/3
    (ecto) lib/ecto/repo/schema.ex:624: Ecto.Repo.Schema.process_children/4
    (ecto) lib/ecto/repo/schema.ex:691: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
    
iex(3) :(

I’m not sure why its blowing up at the db with a unique_constraint when I dont have a unique_constraint set for that field. Also note that I’m not trying to create a new program but rather assign it to the user.

Thoughts?

EDIT: Ok I just now noticed the insert into the programs. INSERT INTO "programs"
What do I need to do to only make the link to programs but not create one?

EDIT 2: I think I understand now, it is not enough to just pass an id, but rather I need to preload the struct for that given association. I"m got the error because I assumed that the id alone was enough to satisfy the insert into the join table. The mistake I made was ecto didn’t see the struct as an already existing row because its not preloaded and thus tried to make a new Product with the same ID causing the constraint error on the primary key. Had I read the documentation better I would have probably understood this better.

When updating the data, this function requires the association to have been preloaded in the changeset struct. Missing data will invoke the :on_replace behaviour defined on the association. Preloading is not necessary for newly built structs
– Ecto.Changeset — Ecto v3.11.1

The next step I have is to learn about the best pattern for preloading like this. One idea from the slack channel was.

I needed to create m2m association and I solved it by using a virtual field which stores the selected IDs from the multiple select and then I create the records manually – @amarsahinovic

I guess that makes the most logical sense

Any how I’ll leave this hear in hopes it helps the next person.
[/quote]

**_

I have user who I want to associate to many programs.
I have a program that can have many users.

I want to be able to select multiple programs via a multi select while I edit a given user. So from there I only have IDs to work with in my passed params.

So lets start with my migration

defmodule MyApp.Repo.Migrations.CreateJoinProgramsUsers do
  use Ecto.Migration

  def change do
    create table(:programs_users, primary_key: false) do
      add :program_id, references(:programs)
      add :user_id, references(:users)
    end
  end
end

I feel confident that the migration is correct but here it is for reference just incase.

Now on to the my user class.

defmodule MyApp.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset
  alias MyApp.Accounts.User

  schema "users" do
    ...

    many_to_many :programs, MyApp.Organization.Program, join_through: "programs_users"

    ...
  end

  @doc false
  def changeset(%User{} = user, attrs) do
    user
    |> MyApp.Repo.preload(:programs)
    |> cast(attrs, [...])
    |> put_assoc(:programs, pull_programs(attrs))
  end

  
  def pull_programs(attrs) do
    case attrs do
      %{"programs" => _} ->
        attrs["programs"]
        |> Enum.map fn(id) ->
          {id, ""} = Integer.parse(id)
          %{id: id }
        end
      _ ->
        []
    end
  end
end

I dont feel confident that I have the changeset setup correct. I could use a review on that. (“read below: its because I don’t :sob:”)

Ok so heres where I’m having an issue.

iex(1)> user = Accounts.get_user!(3)

%MyApp.Accounts.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
 ...
 programs: [],
 ...
 }

iex(2)> Accounts.update_user(user, %{"programs" => ["1"]})

[debug] QUERY OK db=0.3ms
begin []
[debug] QUERY ERROR db=2.1ms
INSERT INTO "programs" ("id","inserted_at","updated_at") VALUES ($1,$2,$3) [1, {{2017, 9, 29}, {20, 42, 18, 606804}}, {{2017, 9, 29}, {20, 42, 18, 606812}}]
[debug] QUERY OK db=0.4ms
rollback []
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * unique: programs_pkey

If you would like to convert this constraint into an error, please
call unique_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

    (ecto) lib/ecto/repo/schema.ex:495: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir) lib/enum.ex:1255: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/schema.ex:481: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto) lib/ecto/repo/schema.ex:213: anonymous fn/13 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/association.ex:953: Ecto.Association.ManyToMany.on_repo_change/4
    (ecto) lib/ecto/association.ex:330: anonymous fn/7 in Ecto.Association.on_repo_change/6
    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/association.ex:327: Ecto.Association.on_repo_change/6
    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/association.ex:293: Ecto.Association.on_repo_change/3
    (ecto) lib/ecto/repo/schema.ex:624: Ecto.Repo.Schema.process_children/4
    (ecto) lib/ecto/repo/schema.ex:691: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
    
iex(3) :(

I’m not sure why its blowing up at the db with a unique_constraint when I dont have a unique_constraint set for that field. Also note that I’m not trying to create a new program but rather assign it to the user.

Thoughts?

EDIT: Ok I just now noticed the insert into the programs. INSERT INTO "programs"
What do I need to do to only make the link to programs but not create one?

EDIT 2: I think I understand now, it is not enough to just pass an id, but rather I need to preload the struct for that given association. I"m got the error because I assumed that the id alone was enough to satisfy the insert into the join table. The mistake I made was ecto didn’t see the struct as an already existing row because its not preloaded and thus tried to make a new Product with the same ID causing the constraint error on the primary key. Had I read the documentation better I would have probably understood this better.

When updating the data, this function requires the association to have been preloaded in the changeset struct. Missing data will invoke the :on_replace behaviour defined on the association. Preloading is not necessary for newly built structs
– Ecto.Changeset — Ecto v3.11.1

The next step I have is to learn about the best pattern for preloading like this. One idea from the slack channel was.

I needed to create m2m association and I solved it by using a virtual field which stores the selected IDs from the multiple select and then I create the records manually – @amarsahinovic

I guess that makes the most logical sense

Any how I’ll leave this hear in hopes it helps the next person.

2

You can insert values into the join table directly (given the program and user exist) without preloading anything

user_id = 1
program_ids = [1, 3, 5, 7]
inserts = for program_id <- program_ids, do: %{user_id: 1, program_id: program_id}
Repo.insert_many("programs_users", inserts)

or use a changeset, but for that you would create a new module for ProgramUser or something like that

defmodule ProgramUser do
  use Ecto.Schema
  import Ecto.Changeset

  schema "programs_users" do
    belongs_to :user, User
    belongs_to, :program, Program
    # or
    # field :user_id, :integer
    # field :program_id, :integer
    # maybe timestamps()
  end

  def changeset(program_user, attrs) do
    program_user
    |> cast(attrs, [:user_id, :program_id])
    # |> constraints and whatnot
  end
end

There are many ways to insert relations into a join table. I’ve listed two above. You would use them explicitly from within your contexts and not from withing your changesets, making the logic of your app a bit more clear.

I feel confident that the migration is correct but here it is for reference just incase.

You would want to add a unique constraint to avoid duplicate relations.

2 Likes
3

As a side note, for anyone who comes across this thread:

If your preload field has join filters due to your get!/1 function, force the preload using the :force option , as Ecto ignores already preloaded fields for Repo.preload/3

1 Like