Hello,

How do I use correctly the many_to_many association with timestamps?

I suppose it uses insert_all (or update_all) under the put_assoc and it is the reason it doesn’t provide inserted_at and modified_at.

The schemas:

defmodule A.User do
  use Ecto.Schema
  import Ecto.Changeset

  alias A.Product

  schema "users" do
    field :name, :string

    many_to_many :products, Product, join_through: "users_products"

    timestamps()
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:name])
    |> validate_required([:name])
  end
end

defmodule A.Product do
  use Ecto.Schema
  import Ecto.Changeset


  schema "products" do
    field :name, :string

    timestamps()
  end

  @doc false
  def changeset(product, attrs) do
    product
    |> cast(attrs, [:name])
    |> validate_required([:name])
  end
end

defmodule A.UserProduct do
  use Ecto.Schema
  import Ecto.Changeset

  alias A.User
  alias A.Product

  schema "users_products" do
    belongs_to :user, User
    belongs_to :product, Product

    timestamps()
  end

  @doc false
  def changeset(user_product, attrs) do
    user_product
    |> cast(attrs, [])
    |> validate_required([])
  end
end

The users_products migration:

  def change do
    create table(:users_products) do
      add :user_id, references(:users, on_delete: :nothing)
      add :product_id, references(:products, on_delete: :nothing)

      timestamps()
    end

    create unique_index(:users_products, [:user_id, :product_id])
    create index(:users_products, [:user_id])
    create index(:users_products, [:product_id])
  end

The association:

IO.puts("querying")
product = from q in Product,
  where: q.name == ^product_name
product = Repo.one(product)
IO.inspect(product, label: "product:")

IO.puts("querying")
user = from q in User,
  where: q.name == ^user_name,
  preload: :products
user = Repo.one(user)
IO.inspect(user, label: "user:")

if product != nil and user != nil do
  cs = Ecto.Changeset.change(user)
  cs = Ecto.Changeset.put_assoc(cs, :products, [product | user.products])
  IO.inspect(cs, label: "assoc put")
  Repo.update!(cs)
end

The error:

[debug] QUERY OK db=0.2ms queue=0.1ms
begin []
[debug] QUERY ERROR db=9.2ms
INSERT INTO "users_products" ("product_id","user_id") VALUES ($1,$2) [1, 1]
[debug] QUERY OK db=0.1ms
rollback []
** (Postgrex.Error) ERROR 23502 (not_null_violation): null value in column "inserted_at" violates not-null constraint

    table: users_products
    column: inserted_at

Failing row contains (4, 1, 1, null, null).

I am up with other ways to achieve this. I don’t mind to use more explicit forms, but I need flexibility to add/remove/modify the associations, for example something like:

# Check if exists
up = from q in UserProduct,
  where: q.user == ^user.id and q.product == ^product.id
up = Repo.one(up)
IO.inspect(up, label: "user_product")
# Create an association
up = Products.create_user_product(%{"user_id" => 1, "product_id" => 2})
Repo.insert!(up)

But it says UserProduct contains virtual fields (belongs_to), and I don’t know if it is natural to change it to field when they don’t belong in UserProduct but User and Product.