Many to Many - create a new item on the other end - I get inserted at missing null value

Questions / Help
,
1

I have a many_to_many relationship ( I guess there really isn’t such a thing) but a junction table nevertheless.

erDiagram
    USER_GAMES {
        int user_id
        int game_id
    }
    USER {
        int id
        string name
    }
    GAME {
        int id
        string name
    }

  schema "users" do
    field :email, :string
    many_to_many :games, Game, join_through: "users_games"
    timestamps(type: :utc_datetime)
  end

 schema "games" do
    field :name, :string
    field :state, :map
    field :started_at, :utc_datetime
    many_to_many :users, User, join_through: "users_games"
    timestamps(type: :utc_datetime)
  end

No schema for users_games but in the migration

  create table("users_games", primary_key: false) do
      add :user_id,
          references(:users, on_delete: :delete_all),
          null: false

      add :game_id,
          references(:games, on_delete: :delete_all),
          null: false

      timestamps()
    end

    create unique_index(:users_games, [:user_id, :game_id])
  end
end

The action happens here

    new_game = %Game{
      name: attrs.name,
      started_at: attrs.started_at
    }

    user =
      Accounts.get_user_with_games(user_id)
      |> Ecto.Changeset.change()
      |> Ecto.Changeset.put_assoc(:games, [new_game])
      |> Repo.update!()

where get_user_with_games is

    get_user!(user_id)
    |> Repo.preload(:games)

I realise right now I need to include any existing related games but I can get to that later.

I get this

[debug] QUERY OK db=1.0ms idle=895.3ms
begin []
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[debug] QUERY OK source="games" db=1.6ms
INSERT INTO "games" ("name","started_at","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["ABC", ~U[2026-02-12 19:12:43Z], ~U[2026-02-12 19:12:43Z], ~U[2026-02-12 19:12:43Z]]
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[debug] QUERY ERROR source="users_games" db=0.5ms
INSERT INTO "users_games" ("user_id","game_id") VALUES ($1,$2) [1, 6]
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[debug] QUERY OK db=0.2ms
rollback []
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[error] GenServer #PID<0.1178.0> terminating
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "inserted_at" of relation "users_games" violates not-null constraint

    table: users_games
    column: inserted_at

Which looks so close…it creates a transaction creates the game but when it is inserting into users_games it omits the timestamps.

What am I missing?

It looks like building the query

INSERT INTO "users_games" ("user_id","game_id") VALUES ($1,$2) [1, 6]

should know there are timestamp fields that could be populated for this.

Should I sack the many to many and create an intermediate entity and and have two one_to_many relationships and do it manually?

1 Like
2

Do you need the timestamp on the join table? If not, delete it.

If you do need it, I think doing the many-to-many through a join schema should work. Associations — Ecto v3.13.5

edit: also, this section of the guide talks about the differences between having a join schema and not. Polymorphic associations with many to many — Ecto v3.13.5

2 Likes
3

Since you’re using the :join_through option with a string value, there is nothing more that Ecto knows about this join table. It knows the two columns with the foreign keys (using the _id suffix). But it knows nothing about your timestamp columns.

Read the documentation on Ecto.Schema.many_to_many again, and you’ll see they make a distinction between passing in just a string, and passing in a schema.

From the docs:

However, if your :join_through is a schema, like MyApp.PostTag, your join table may be structured as any other table in your codebase, including timestamps

and:

Because :join_through contains a schema, in such cases, autogenerated values and primary keys will be automatically handled by Ecto.

There are many other ways to model many-to-many relations in Ecto. But if the missing timestamp is your only issue right now, I’d define the schema, and you’re all set.

You could also define a default value (NOW()) for a inserted_at column in your migration. Then the database handles setting this value for you, instead of Ecto. But you would still need to model a schema for the join table if you need the timestamp in your application somewhere.

4 Likes
4

This looks precise, logical and excellent.
I missed that bit of the documentation and look forward to trying it tomorrow.

5

I did get this working in the end.

I added a schema for the M2M relation

Note: there is an auto primary key (id)

defmodule MyApp.UsersGames do 
 schema "users_games" do
    belongs_to(:user, User)
    belongs_to(:game, Game)
    timestamps()
  end
end

the relations on the other schemas now reference the above schema

for example,

    many_to_many :users, User, join_through: UsersGames

Then to create the relation and the game

    new_game = %Game{
      name: attrs.name,
      started_at: attrs.started_at
    }

    user =  get_user!(user_id)
    |> Repo.preload(:games)

    games = user.games

    user
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:games, [new_game | games])
    |> Repo.update!()

It is essential as this is a put to collect the games already belonging to the user and add them to list [new_game | games].

Sub-optimal in terms of performance as the users_games since there is a overhead for the id column and the use of put.

It not entirely intuitive and the documentation suggests the intermediate relation schema isn’t necessary.

6

You don’t need that if you configure the schema to not use an :id column. Once you’re using a schema you can use it to provide all manner of additional information to ecto.

Where exactly do you read it like that. Imo the documentation has always been very explicit that no-schema means no additional columns – especially given in the past there was no option for providing a schema at all, so this really needed to be explicit.

1 Like
7

The documentation for Many to Many

Through a join table as an alternative to Through a join schema only include the migration and no schema for the join.

create table("users_organizations", primary_key: false) do
      add :user_id,
          references(:users, on_delete: :delete_all),
          null: false

      add :organization_id,
          references(:organizations, on_delete: :delete_all),
          null: false

      timestamps()
    end

    create unique_index(:users_organizations, [:user_id, :organization_id])

Using a join schema

 @primary_key false
  schema "users_organizations" do
    belongs_to :user, User
    belongs_to :organization, Organization
    timestamps()
  end

also sets primary key to be false which didn’t work for me as the query was returning an id which didn’t exist and threw an error.

8

Ok, those cheatsheets are quite a bit newer. That’s something to be fixed then.

You might also need to declare the foreign key columns to be the primary keys, but granted I never actually tried that.

@primary_key false
schema "users_organizations" do
  field :user_id, :id, primary_key: true
  belongs_to :user, User, define_field: false

  field :organization_id, :id, primary_key: true
  belongs_to :organization, Organization, define_field: false

  timestamps()
end

Edit: I setup an issue for fixing the docs: Cheatsheet many-to-many migration incorrect · Issue #4703 · elixir-ecto/ecto · GitHub

9

I have update the schema and migration according to your suggestions

   @primary_key false
  schema "users_games" do
    field :user_id, :id, primary_key: true
    belongs_to :user, User, define_field: false

    field :game_id, :id, primary_key: true
    belongs_to :game, Game, define_field: false
    timestamps()
  end

and

    create table(:users_games, primary_key: false) do
      add :user_id,
          references(:users, on_delete: :delete_all),
          null: false,
          primary_key: true

      add :game_id,
          references(:games, on_delete: :delete_all),
          null: false,
          primary_key: true

      timestamps()
    end

And this now works without the id column

2 Likes
10

Looks like my attempt at clarifying it got merged. Updates many-to-many guide by jdiago · Pull Request #4704 · elixir-ecto/ecto · GitHub

1 Like