modify ecto many_to_many schema

Question

Having this schema

many_to_many :customers, User,
  join_through: Customer, on_replace: :delete

I want to add an on_delete clause:

on_delete: :delete_all

So I modified the schema with

many_to_many :customers, User,
  join_through: Customer, on_replace: :delete,
  on_delete: :delete_all

And created a migration, but as it's a many to many and it creates a new table I dont know how can you reference the field, I have searched through ecto docs but couldnt find an example covering this case:

defmodule Migration do
  use Ecto.Migration

  def change do
    alter table(:products) do
      modify :customers, references(:customers, on_delete: :delete_all)
    end
  end
end

But when running the migration it obviously tells me the column customers doesnt exist:

(undefined_column): column "customers" of relation "products" does not exist

On iex it's shown as an ecto association

customers: #Ecto.Association...

To sum it up, basically, I want to delete the customer when the product is deleted.

sweeneydavidj sweeneydavidj · Accepted Answer · 2018-04-04T22:13:03

For many_to_many relationships a join table must be used. For example where a User may have many Roles and a Role may belong to many Users...

schema "users" do
  field(:username, :string)
  many_to_many(:roles, Role, join_through: UserRole, on_replace: :delete, on_delete: :delete_all)

schema "users_roles" do
  belongs_to(:user, User)
  belongs_to(:role, Role)

schema "roles" do
  field(:name, :string)
  # optional - if you need to use the relationship in the "reverse" direction.
  # many_to_many(:users, User, join_through: UserRole)

Documentation for many_to_many options

The foreign key in the table corresponds to the belongs_to in the schema

create table(:users, primary_key: false) do
  add :id, primary_key: true
  add :username, :string, null: false
end

create table(:users_roles, primary_key: false) do
  add :user_id, references(:users, on_delete: :delete_all), null: false
  add :role_id, references(:roles, on_delete: :delete_all), null: false
end

create table(:roles, primary_key: false) do
  add :id, :binary_id, primary_key: true
  add :name, :string, null: false
end

Documentation for references options

modify ecto many_to_many schema

1 Answers