A University of College London study discovered something we all knew: humans are lazy. Meaning that most of the times, when we have a problem we take the easiest way to solve it. But it doesn’t mean we are solving the problem right away.

You might ask yourself, what does this have to do with Elixir and Ecto? Well, at Coletiv we have been working on a project where a user can manage multiple projects and a project can be managed by multiple users, this is a trivial case known as many to many.

In terms of database diagram/structure, I created both user and project tables, plus the so-called Many to Many table (user_project), as the diagram below illustrates.

Database with many to many table

First implementation — Scaffolding all

At first, I thought the easiest way to solve the problem was to scaffold each table and manually create a method that directly managed the projects from a user in the user_project table.

It worked perfectly fine, the unit tests proved the solution worked just fine!

Step 1

Create a migration that adds user_project table.

defmodule MyApp.Repo.Migrations.AddTableUserProject do use Ecto.Migration def change do create table(:user_project, primary_key: false) do add(:project_id, references(:project, on_delete: :delete_all), primary_key: true) add(:user_id, references(:user, on_delete: :delete_all), primary_key: true) timestamps() end create(index(:user_project, [:project_id])) create(index(:user_project, [:user_id])) end end

Step 2

Create module UserProject with schema and changeset.

defmodule MyApp.Management.UserProject do @moduledoc """ UserProject module """ use Ecto.Schema import Ecto.Changeset alias MyApp.Accounts.User alias MyApp.Management.Project @already_exists "ALREADY_EXISTS" @primary_key false schema "user_project" do belongs_to(:user, User, primary_key: true) belongs_to(:project, Project, primary_key: true) timestamps() end @required_fields ~w(user_id project_id)a def changeset(user_project, params \\ %{}) do user_project |> cast(params, @required_fields) |> validate_required(@required_fields) |> foreign_key_constraint(:project_id) |> foreign_key_constraint(:user_id) |> unique_constraint([:user, :project], name: :user_id_project_id_unique_index, message: @already_exists ) end end

Step 3

Create a context function that updates the projects a user has access to. This naive solution deleted all current projects and inserted the list of projects passed as a parameter. This means that if, for example, we were adding a new project to the user and keeping all the old ones, we would still have to delete the old ones and re-add them again with the new list.

def upsert_user_projects(user_id, project_ids) when is_list(project_ids) do {:ok, time} = Ecto.Type.cast(:naive_datetime, Timex.now()) project_users = project_ids |> Enum.uniq() |> Enum.map(fn project_id -> %{ user_id: user_id, project_id: project_id, inserted_at: time, updated_at: time } end) multi = Multi.new() |> Multi.delete_all( :user_project_deleted, UserProject |> where([user_project], user_project.user_id == ^user_id) ) |> Multi.insert_all(:user_project_inserted, UserProject, project_users) case Repo.transaction(multi) do {:ok, _multi_result} -> {:ok, Accounts.get_user(user_id)} {:error, changeset} -> {:error, changeset} {:error, _, changeset, _} -> {:error, changeset} end end

The right way — Use Ecto powers

One of the things we don’t facilitate on our work is pull requests and code reviews. As soon as I did a pull request with these changes, my fellow colleagues immediately refused to merge the pull request and asked: “Shouldn’t Ecto manage the link between users and projects for you?”. This kept me thinking for a while 🤔.

I decided to get back to the drawing board and started reading the Ecto documentation again until I found the put_assoc/4 function that did just what I was looking for: manage many_to_many associations. But it didn’t work straight away.

When you use many_to_many and put_assoc/4 you should be aware of the preloads, you need to ensure that the many_to_many relationship data is loaded so that it is updated. And don’t forget to use the on_replace option when declaring the relationship on your model. It took me a while to figure out why the relationship was not being updated, kudos for David Magalhães for the help.

If you are still confused, please check the steps below:

Step 1

Create a migration that adds user_project table.

defmodule MyApp.Repo.Migrations.AddTableUserProject do use Ecto.Migration def change do create table(:user_project, primary_key: false) do add(:project_id, references(:project, on_delete: :delete_all), primary_key: true) add(:user_id, references(:user, on_delete: :delete_all), primary_key: true) timestamps() end create(index(:user_project, [:project_id])) create(index(:user_project, [:user_id])) create( unique_index(:user_project, [:user_id, :project_id], name: :user_id_project_id_unique_index) ) end end

Step 2

Add many_to_many relations in the Project and User module.

... def MyApp.Management.Project do ... many_to_many( :users, User, join_through: "user_project", on_replace: :delete ) ... end ... def MyApp.Accounts.User do ... many_to_many( :projects, Project, join_through: "user_project", on_replace: :delete ) ... end

Step 3.

Add new changeset to user module for editing projects (adding the put_assoc/4)

def MyApp.Accounts.User do ... def changeset_update_projects(%User{} = user, projects) do user |> cast(%{}, @required_fields) # associate projects to the user |> put_assoc(:projects, projects) end ... end

Step 4

Create a function that makes use of the changeset to update the projects of a user.

def upsert_user_projects(user, project_ids) when is_list(project_ids) do projects = Project |> where([project], project.id in ^project_ids) |> Repo.all() with {:ok, _struct} <- user |> User.changeset_update_projects(projects) |> Repo.update() do {:ok, Accounts.get_user(user.id)} else error -> error end end

Lesson learned

In short, the lesson to take from here is to:

not always go for the easiest solution as it might not be the best in the long term. And always take time to learn, evolve and experiment new ways of doing things so that you don’t get stuck with outdated techniques.