Ecto: many_to_many timestamps

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.

Maybe try replacing it with

many_to_many :products, Product, join_through: A.UserProduct

@idiot Thank you. It worked. Great that is so easy.

Last question: With this pattern, should we use the put_assoc with User changeset to modify the UserProduct schema (users_products table) or can we use another way (directly by using the UserProduct repo)?

Do you mean this line? I don’t know how put_assoc works with many to many relationships, if it diffs what you intend to insert with what’s already there (in the table), and makes the repo to insert only that difference, then it’s fine, probably. If it causes the repo to delete what was there (in the table) before and reinsert all entries again, then some other way would be preferred.

Try inserting / updating / reinserting several many-to-many relationships and watch the logged sql statements.

I think I usually deal with these via Repo.insert_all.

@idiot Exactly. I will check it out. I assume you use this pattern (put_assoc). I thought that maybe with complex relations there is a more manual way to deal with it.