How to insert multiple has_many associations (lookup table)

I’m trying to figure out the “best” approach to inserting 1 or more associations in a single go. The Ecto associations API made me think there would be an easy way to create several associations and then put or insert the whole batch in a single shot.

After much experimenting, it seems the best answer is… maybe don’t use associations.

Here’s my test schema:

  schema "users" do
    field :email, :string

    has_many :roles, Dma.Accounts.Role, on_replace: :delete_if_exists

  @primary_key false
  schema "roles" do
    field :has_role, Ecto.Enum, values: [:is_admin, :is_team_lead, :is_company_admin], primary_key: true

    belongs_to :user, Dma.Accounts.User, primary_key: true

Pretty simple… a User has zero or more Roles. The roles table is simply a pair of [user_id, has_role] (where the latter is basically just a string, mapped to atoms courtesy of Ecto). When I create a new User they don’t necessarily have roles… those are added later on a case-by-case basis. A User can have none or a multiple, such as is_admin and is_team_lead.

The intended API is like so, which allows for adding or removing one or more Roles in a single call:

   |> Accounts.assign_roles([:is_admin, :is_team_lead], true)
   |> ...

My first attempt at an API looked like this:

  def assign_roles(id, roles, true) when is_list(roles) do
    for role <- roles do
      %User{id: id}
      |> Ecto.build_assoc(:roles, %{has_role: role})
      |> Repo.insert()


The intended API is that a User (with all Roles preloaded) would be returned from the call – basically so that I can be sure my LiveView is reflecting a true picture of this user’s roles.

This solution is incomplete, as it throws an Ecto.ConstraintError if a duplicate record is inserted. My desired behavior is to ideally not attempt to insert a dupe, or at least, ignore any errors – so I moved on to this:

  def assign_roles(id, roles, true) when is_list(roles) do
    for role <- roles do
      %Role{user_id: id}
      |> Ecto.Changeset.change(has_role: role)
      |> Ecto.Changeset.unique_constraint(:user_id, name: :roles_pkey)
      |> Repo.insert


This works, but is more verbose. It’s seems unnecessary.

I finally settled on this:

  def assign_roles(id, roles, true) when is_list(roles) do
    for role <- roles do
      Repo.insert!(%Role{user_id: id, has_role: role}, on_conflict: :nothing)


And that brings me to my questions:

  1. Have I missed the point somehow? Originally I was trying to use put_assoc (or variations) and do this through the Ecto associations API. It just didn’t seem to work out that well. But I feel there should be a way that works well.
  2. I don’t like having to reload the User at the end. On the other hand – there’s really no race condition to worry about… I just want to make sure that when the call is done, I’ve got a current snapshot of the User and Roles. Is there a better way?
  3. I haven’t tackled deleting yet… I think the typical approach is to nil the unwanted Role’s but I’m wondering if it just makes sense to delete them (e.g., use my Repo to effectively “delete from roles where user_id=x and has_role=y”).

Any comments / suggestions / pointers to how to do it right much appreciated.

1 Like

For anyone that’s interested, here’s what I ended up implementing for revoking roles:

  def revoke_roles(%User{id: id}, roles), do: assign_roles(id, roles, false)

  def assign_roles(id, roles, false) when is_list(roles) do
    from(r in Role, where: r.user_id == ^id and r.has_role in ^roles) |> Repo.delete_all


End result of wiring it up to a LiveView is pretty simple. Each role is shown as a toggle (checkmark) on the page, and can be toggled on/off with a click. The command parameter is the role I want toggled, and I pass in the current state and just flip it to toggle to a different state.

  def handle_event(command, %{"id" => sid, "value" => current_state}, socket) do
    id = String.to_integer(sid)
    Accounts.assign_roles(id, [String.to_atom(command)], ! As.bool!(current_state))

    {:noreply, update(socket, :users, &, fn user -> if( == id, do: Accounts.get_user!(id), else: user) end ))}

Here’s the LiveView code:

<:col :let={user} label="Admin" class="w-32">
   <button phx-click="is_admin" phx-value-id={} value={to_string(Accounts.is_admin?(user))}>
      <%= display_check?(Accounts.is_admin?(user)) %>

Would love to hear what other approaches have been taken, or critique – looking for how to make it scalable and efficient.

If you want to operate on roles through its parent user, the most recent versions of Ecto extended cast_assoc/3 to accept a :drop_param option that simplifies deleting through has_many associations.

%{"name" => "john doe", "addresses" => %{
  0 => %{"street" => "somewhere", "country" => "brazil", "id" => 1},
  1 => %{"street" => "elsewhere", "country" => "poland"}

Using indexes becomes specially useful with two supporting options: :sort_param and :drop_param. These options tell the indexes should be reordered or deleted from the data. For example, if you did:

cast_embed(changeset, :addresses,
  sort_param: :addresses_sort,
  drop_param: :addresses_drop)

You can now submit this:

%{"name" => "john doe", "addresses" => %{...}, "addresses_drop" => [0]}

And now the entry with index 0 will be dropped from the params before casting. Note this requires setting the relevant :on_replace option on your associations/embeds definition.

source: Sorting and deleting from -many collections | Ecto.Changeset.cast_assoc/3 docs