Many to many relationship with Ecto

many_to_many relationships_in_ecto.md

1. Create the tables

see migration.ex
then
mix ecto.migrate

2. Create the schemas

see:
- role.Ex
- permission.ex
- role_permission.ex

3. Use relationship

3.1. Create / Update

import Ecto.Changeset  
alias ManyToManyExample.Repo  
alias ManyToManyExample.Role  
alias ManyToManyExample.Permission  

# Add a role
role = Role.changeset(%Role{}, %{name: "Test role"})
   |> Repo.insert!()
   
# Add a permission
permission = Permission.changeset(%Permission{}, %{name: "Test permission"})
   |> Repo.insert!()
   
# Add permission to role
role 
|> Repo.preload(:permissions) 
|> change()  
|> put_assoc(:permissions, [permission]) 
|> Repo.update!()

3.2. Update

import Ecto.Changeset  
alias ManyToManyExample.Repo  
alias ManyToManyExample.Role  
alias ManyToManyExample.Permission  

# Get role with permissions
role = Repo.get_by! Role, name: "Test role"
  |> Repo.preload(:permissions)
   
# Add a new permission
permission = Permission.changeset(%Permission{}, %{name: "New permission"})
   |> Repo.insert!()
   
# Add permission to role
role 
|> change()  
|> put_assoc(:permissions, role.permissions ++ [permission]) 
|> Repo.update!()

3.2. Delete

import Ecto.Changeset  
alias ManyToManyExample.Repo  
alias ManyToManyExample.Role  
alias ManyToManyExample.Permission  

# Get role with permissions
role = Repo.get_by! Role, name: "Test role"
  |> Repo.preload(:permissions)
   
# Add a new permission
permission = Permission.changeset(%Permission{}, %{name: "New permission"})
   |> Repo.insert!()
   
# Remove permission from role
role 
|> change()  
|> put_assoc(:permissions, role.permissions -- [permission]) 
|> Repo.update!()

migration.ex

defmodule ManyToManyExample.Migrations.AddRoles do
  use Ecto.Migration

  def change do
    # Permissions table
    create table(:permissions) do
      add :name, :string

      timestamps()
    end

    # Roles table
    create table(:roles) do
      add :name, :string

      timestamps()
    end

    # Permission to role table
    # This table doesn't need primary key
    create table(:role_permissions, primary_key: false) do
      add :role_id, references(:roles, on_delete: :delete_all,
                               on_update: :update_all)
      add :permission_id, references(:permissions, on_delete: :delete_all,
                                     on_update: :update_all)

      timestamps()
    end

    # Role <-> permission association should be unique
    create unique_index(:role_permissions, [:role_id, :permission_id])
  end
end

permission.ex

defmodule ManyToManyExample.Permission do
  use Ecto.Schema
  import Ecto
  import Ecto.Changeset

  alias ManyToManyExample.Role
  alias ManyToManyExample.RolePermission

  @required_fields ~w(name)

  schema "permissions" do
    field :name, :string
    many_to_many :roles, Role, join_through: RolePermission, on_replace: :delete

    timestamps()
  end

  def changeset(model, params \\ %{}) do
    model
    |> cast(params,  @required_fields)
    |> validate_required(Enum.map @required_fields, &String.to_atom/1)
    |> validate_length(:name, min: 3, max: 40)
  end
end

role.ex

defmodule ManyToManyExample.Role do
  use Ecto.Schema
  import Ecto.Changeset

  alias ManyToManyExample.RolePermission
  alias ManyToManyExample.Permission

  @required_fields ~w(name)

  schema "roles" do
    field :name, :string
    many_to_many :permissions, Permission, join_through: RolePermission, on_replace: :delete

    timestamps()
  end

  def changeset(model, params \\ %{}) do
    model
    |> cast(params,  @required_fields)
    |> validate_required(Enum.map @required_fields, &String.to_atom/1)
    |> validate_length(:name, min: 3, max: 40)
  end
end

role_permission.ex

defmodule ManyToManyExample.RolePermission do
  use Ecto.Schema
  import Ecto.Changeset

  @required_fields ~w(role_id, permission_id)

  @primary_key false
  schema "role_permissions" do
    belongs_to :role, ManyToManyExample.Role
    belongs_to :permission, ManyToManyExample.Permission

    timestamps();
  end
end