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?

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

1 Like
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.