many_to_many batch insert/update

Question

I am trying to figure out what is the best way to save struct with many_to_many relationship.

Let's say I have user and role schema created in following way:

migration:

def change do
 create table(:users) do
   add :email, :string
   add :username, :string
 end

 create table(:roles) do
   add :name, :string, null: false
 end

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

 create unique_index(:users, [:email, :username]
 create unique_index(:roles, [:name])
 create unique_index(:users_roles, [:user_id, :role_id])
end

user schema:

schema "users" do
  field :email, :string
  field :username, :string
  many_to_many :roles, TimesheetServer.Role, join_through: "users_roles"
 end

  def changeset(struct, params \\ %{}) do
   struct
    |> cast(params, [:email, :username])
    |> validate_required([:email, :username])
  end
end

role schema:

schema "roles" do
     field :name, :string
     many_to_many :users, TimesheetServer.User, join_through: "users_roles"
   end

   def changeset(struct, params \\ %{}) do
     struct
     |> cast(params, [:name])
     |> validate_required([:name])
   end
 end

There is no problem with association when it comes to associating a single role with a user. However, when it comes to associating multiple roles in one request I came up only with the following solution:

update user endpoint: /api/users/{user_id}

request body:

{
  "roles": [
    {"id": 10},
    {"id": 2},
    {"id": 1}
  ]
}

user changeset:

  def changeset(struct, params \\ %{}) do
     struct
      |> cast(params, [:email, :username])
      |> validate_required([:email, :username])
      |> put_assoc(:roles, cast_roles(params))
  end

  defp cast_roles(params) do
    %{"roles" => roles} = params
    Enum.map(roles, fn role -> get_role(role) end)
  end

  defp get_role(role) do
    %{"id" => id} = role
    Repo.get!(Role, id)
  end

That does work, the association is updated. What I am worried about is that I have to iterate over roles and do DB request for each of them. Is there any other way to achieve the same result?

I usually create a module/schema for users_roles and then adding an association becomes just Repo.insert %UserRole{user_id: user_id, role_id: role_id} (after appropriate validations, if needed). — Dogbert
So, in that case I would just create multiple %UserRole structs and then do Repo.insert_all, right? — jmac

Dogbert Dogbert · Accepted Answer · 2017-06-09T15:58:10

You can create a module/schema for the association which points to the users_roles table and then just insert a %UserRole{} struct to create an association between a User and a Role. This way you can also use Repo.insert_all to do bulk inserts.

defmodule MyApp.UserRole do
  ...
  schema "users_roles" do
    belongs_to :user, User
    belongs_to :role, Role
  end
end

Repo.insert! %MyApp.UserRole{user_id: 123, role_id: 456}

or

Repo.insert_all! MyApp.UserRole, [%{user_id: 1, role_id: 1},
                                  %{user_id: 2, role_id: 1},
                                  %{user_id: 2, role_id: 2}]

many_to_many batch insert/update

1 Answers