Why primary key does not autogenerate when doing many to many assoc?

Phoenix Forum Questions / Help
,
1

user model

defmodule App.Accounts.User do
  use Ecto.Schema

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  alias App.{Accounts, Roles}

  schema "users" do
    field :username, :string

    many_to_many :permissions, Roles.Permission, join_through: "users_permissions"
  end
end

permission model

defmodule App.Roles.Permission do
  use Ecto.Schema

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  alias App.{Accounts}

  schema "permissions" do
    field :action, :string

    many_to_many :users, Accounts.User, join_through: "users_permissions"
  end
end

users_permissions migration file

defmodule App.Repo.Migrations.AddUserPermissionManyToManyTable do
  use Ecto.Migration

  def change do
    create table(:users_permissions, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :user_id, references(:users, on_delete: :delete_all, type: :uuid), null: false
      add :permission_id, references(:permissions, on_delete: :delete_all, type: :uuid), null: false

      timestamps([{:updated_at, :false}])
    end

    create unique_index(:users_permissions, [:user_id, :permission_id])
  end
end

seed file

# ...omit body
master_account =
  %Accounts.User{}
    |> Accounts.User.registration_changeset(%{...})
    |> Repo.insert!()
    |> Repo.preload(:permissions)

action_lists = ["register", ...]

Enum.each(action_lists, fn(action) ->
  Ecto.Multi.new()
    |> Ecto.Multi.insert(:permissions, %Roles.Permission{action: action})
    |> Ecto.Multi.merge(&(
      &1[:permissions]
        |> Repo.preload(:users)
        |> Ecto.Changeset.change()
        |> Ecto.Changeset.put_assoc(:users, [master_account])
        |> Repo.update()
    ))
    |> Repo.transaction()
end)

console logs

** (Postgrex.Error) ERROR 23502 (not_null_violation): null value in column "id" violates not-null constraint

table: users_permissions
column: id

Failing row contains (null, 692a0877-6b7d-4e32-a2ce-1c48ffd86064, f0f27bc1-b0be-45af-8686-439a7500a351, null).
(ecto) lib/ecto/adapters/sql.ex:200: Ecto.Adapters.SQL.query!/5
(ecto) lib/ecto/adapters/postgres.ex:96: Ecto.Adapters.Postgres.insert_all/7
(ecto) lib/ecto/repo/schema.ex:52: Ecto.Repo.Schema.do_insert_all/7
(ecto) lib/ecto/association.ex:1031: Ecto.Association.ManyToMany.on_repo_change/4
(ecto) lib/ecto/association.ex:338: anonymous fn/7 in Ecto.Association.on_repo_change/6
(elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/association.ex:335: Ecto.Association.on_repo_change/6
(elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/association.ex:301: Ecto.Association.on_repo_change/3
(ecto) lib/ecto/repo/schema.ex:708: Ecto.Repo.Schema.process_children/4
(ecto) lib/ecto/repo/schema.ex:774: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
(db_connection) lib/db_connection.ex:1374: DBConnection.transaction_nested/2
(db_connection) lib/db_connection.ex:1234: DBConnection.transaction_meter/3
(db_connection) lib/db_connection.ex:798: DBConnection.transaction/3
(ecto) lib/ecto/multi.ex:447: Ecto.Multi.apply_operation/4
(ecto) lib/ecto/multi.ex:421: Ecto.Multi.apply_operation/5
(elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/multi.ex:411: anonymous fn/5 in Ecto.Multi.apply_operations/5
(ecto) lib/ecto/adapters/sql.ex:576: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
(db_connection) lib/db_connection.ex:1283: DBConnection.transaction_run/4

also the timestamps seems to be null also, which from the console i can see the permission_id and user_id but not the primary id and inserted_at timestamps

I also tried to add this file into the lib/app/roles/user_permission.ex but it still give me the same error

# user_permission.ex

use Ecto.Schema

@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id

alias App.{Accounts, Roles}

schema "users_permissions" do
  belongs_to :user, Accounts.User
  belongs_to :permission, Roles.Permission

  timestamps([{:updated_at, :false}])
end
1 Like
2

Ignore - I just read this:

given :through associations are read-only

After this remark I have to wonder whether many_to_many may be better represented as:

# User.ex

has_many :user_permissions, Accounts.UserPermission
has_many :user_actions, through: [:user_permissions, :permission]

# :user_permissions - has_many association in this schema, the User
# :permission       - belongs_to association in UserPermission schema

See Ecto.Schema: has_many/has_one :through

Another has_many :through example.

1 Like
3

That Ecto.Multi.merge/2 shouldn’t even work:

  Ecto.Multi.new()
    |> Ecto.Multi.insert(:permissions, %Roles.Permission{action: action})
    |> Ecto.Multi.merge(&(
      &1[:permissions]
        |> Repo.preload(:users)
        |> Ecto.Changeset.change()
        |> Ecto.Changeset.put_assoc(:users, [master_account])
        |> Repo.update()
    ))
    |> Repo.transaction()

given that the function is supposed to return another Ecto.Multi structure . I had to resort to

    genre = %Genre{ name: "live2", wiki_tag: "Concert2" }
    query = from(a in Album, [
        where: a.title == "Live At Montreaux",
        preload: :genres
      ])
    album = Repo.one(query)
    multi =
      Multi.new
      |> Multi.insert(:genres, genre)
      |> Multi.merge(fn result ->
          cs =
            result[:genres]
            |> Repo.preload(:albums)
            |> Ecto.Changeset.change()
            |> Ecto.Changeset.put_assoc(:albums, [album])

            Multi.new
            |> Multi.update(:genre_album, cs)
         end)
    Repo.transaction(multi)

    Repo.one(query)
  end

to get it to work. As this example uses the standard :id type

INSERT INTO "albums_genres" ("album_id","genre_id") VALUES ($1,$2) [5, 8]

it works fine, as the database generates the primary keys. So the issue is likely related to the externally supplied UUID values.

Given that you’re inside a multi it may make more sense to just insert the records manually (and generate the UUIDs manually if necessary).

    genre = %Genre{ name: "live2", wiki_tag: "Concert2" }
    query = from(a in Album, [
        where: a.title == "Live At Montreaux",
        preload: :genres
      ])
    album = Repo.one(query)
    multi =
      Multi.new
      |> Multi.insert(:genres, genre)
      |> Multi.merge(fn result ->
           album_genre = [
             album_id: album.id,
             genre_id: Map.get(result[:genres], :id)
           ]

           Multi.new
           |> Multi.insert_all(:genre_album, "albums_genres", [album_genre])
         end)
    Repo.transaction(multi)

    Repo.one(query)
  end

end
1 Like
4

Thanks @peerreynders for your time by answering to this and sorry for my late reply because my internet could not access the forum sometime.
I will consider about the first case that you mentioned to try it out.

At the mean time, I found a way to get around it but I am not sure if this is the prefer way, so here is the migration file

use Ecto.Migration

def change do
  execute "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"

  create table(:users_permissions, primary_key: false) do
    add :id, :uuid, primary_key: true, default: fragment("uuid_generate_v5(uuid_generate_v4(), '#{System.get_env("UUID_V5_SECRET")}')"), read_after_writes: true
    add :user_id, references(:users, on_delete: :delete_all, type: :uuid), null: false
    add :permission_id, references(:permissions, on_delete: :delete_all, type: :uuid), null: false

    add :inserted_at, :naive_datetime, default: fragment("now()")
  end

  create index(:users_permissions, [:user_id, :permission_id])
end

I have change the way to insert from using Ecto.Multi to this below:

register_permission = %Roles.Permission{action: "register"}
  |> Repo.insert!()
  |> Repo.preload(:users)

delete_permission = ...
block_permission = ...

master = %Accounts.User{}
  |> Repo.preload(:permissions)
  |> Accounts.User.registration_changeset(%{
      username: "master",
      password: "very-secret-pw",
      email: "awesome@mail.com"
    })
  |> Ecto.Changeset.put_assoc(:permissions, [register_permission, ...])
  |> Repo.insert!()

The code worked as expected, but is it the preferred way to insert association like this?

I will also try the first case now and see how it works and I will also try to use the Ecto.Multi also as I do not want to insert them one by one like that

1 Like
5

I have changed it to using Ecto.Multi and it also work as expected now.

# seed file

action_lists = ["register", ...]

master = %Accounts.User{}
  |> Repo.preload(:permissions)
  |> Accounts.User.registration_changeset(%{...})
  |> Repo.insert!()

Enum.each(action_lists, fn action ->
  Ecto.Multi.new()
    |> Ecto.Multi.insert(:permission, %Roles.Permission{action: action})
    |> Ecto.Multi.merge(fn result ->
        changeset = result[:permission]
          |> Repo.preload(:users)
          |> Ecto.Changeset.change()
          |> Ecto.Changeset.put_assoc(:users, [master])

        Ecto.Multi.new()
          |> Ecto.Multi.update(:users_permissions, changeset)
      end)
    |> Repo.transaction()
end)
1 Like
6

Thank you, hengly

I have the same issue, and solved by following you.

1 Like
7

:join_through option of Ecto.Schema.many_to_many accepts 2 types of arguments:

  • a string table name
  • a schema

As describe at here, when using a string table name, you may define a table without primary keys and you must not include any further columns, as those values won’t be set by Ecto.

So, the reason why primary key doesn’t autogenerate is you are using a string table name.

If you want to let Ecto handle primary key or timestamps, try to join tables with a schema.

I saw you have declared user_permissions.ex file which should contain UsersPermissions schema. The example code would like this:

many_to_many :permissions, Roles.Permission, join_through: UsersPermissions
# ...
many_to_many :users, Accounts.User, join_through: UsersPermissions
3 Likes
8

Wow. Thank you, c4710n