Remove association (for many to many) in Ecto

Question

I have a User and Program model which have a many-to-many association. They create associations successfully, so that a User has many Programs (and vice versa), but now I need to remove the association (but not delete the User or Program)

I have a function in the ProgramController that associates them like so:

# Myapp.ProgramController

def associate_user(conn, _params) do
   %{"_csrf_token" => _csrf_token,"_method"=>_method,"_utf8" => _utf8,"id" => id, "user" => %{"email" => email}} = _params
   {pid, _} = Integer.parse(id)

   user = Repo.get_by(User, email: email) |> Repo.preload(:programs)
   program = Repo.get_by(Program, id: pid) |> Repo.preload(:users)

   pid_list = user.programs
   |> Enum.map(fn x -> x.id end)
   |> List.insert_at(0, program.id)

   changeset_list = from(program in Myapp.Program, where: program.id in ^pid_list)
   |> Repo.all
   |> Enum.map(&Ecto.Changeset.change/1)

   from(user in User, where: user.email == ^user.email, preload: [:programs, :role])
   |> Repo.one
   |> User.changeset(%{})
   |> Ecto.Changeset.put_assoc(:programs, changeset_list)
   |> Repo.update!

   program = conn.assigns[:program]
   changeset = Program.changeset(program)

   conn
   |> put_flash(:info, "Program updated successfully.")
   |> redirect(to: program_path(conn, :edit, program))
 end

and I have a migration that creates a join table

defmodule Myapp.Repo.Migrations.AssociateUsersAndPrograms do
  use Ecto.Migration

  def change do

    alter table(:users) do
       add :current_program_id, references(:programs, on_delete: :nilify_all)
    end

    create table(:users_programs, primary_key: false) do
      add :user_id, references(:users, on_delete: :delete_all)
      add :program_id, references(:programs, on_delete: :delete_all)
    end

    create index(:users_programs, [:user_id])
  end
end

How would I go about disassociating a particular user from a program as I did not see a delete_assoc in the Ecto.Docs?

Still learning and loving Elixir (and Phoenix) so far so any help would be much appreciated!

Could you make an Ecto model/schema associated with your join table? defmodule MyApp.UserProgram do ... with schema "users_programs" do ... Then you could use Ecto's api to delete the UserProgram with the matching user_id and program_id. — Bruce
This will also allow you to easily add metadata to associations (in future maybe a user is only associated with a program until a specific date so you could add an expires_at field to the UserProgram schema and update it using Ecto). — Bruce
Is that function only really adding an association between 1 program and 1 user? If so, that looks really inefficient. That shouldn't take more than 3 queries, one to fetch program, one to fetch user, one to insert an entry in the join table. I agree with Bruce about creating a schema for the join table. — Dogbert
@Dogbert yeah, I was still just learning back then and now I realized it could be done much more efficiently :) — phil

KA01 KA01 · Accepted Answer · 2016-08-25T01:53:03

You'll probably want to look at the Ecto.Schema.ManyToMany docs. There are a few options (taken and reformatted directly from docs):

:on_delete- The action taken on associations when the parent record is deleted.
:nothing (default)
:delete_all- will only remove data from the join source, never the associated records. Notice :on_delete may also be set in migrations when creating a reference. If supported, relying on the database via migrations is preferred. :nilify_all and :delete_all will not cascade to child records unless set via database migrations.

Remove association (for many to many) in Ecto

2 Answers