Ecto Many to Many

Let us start directly

Step 1: Create Ecto table as usual

Step 2: Set many to many relationships:

schema "users" do
  field :email, :string
  field :name, :string
  many_to_many :posts, Post, join_through: "users_posts", 
  on_replace: :delete
  timestamps([type: :utc_datetime_usec])
end
@doc false
def changeset(user, attrs) do
 user
 |> cast_assoc(:posts, required: false)
 end
end

Same in another table

@timestamps_opts [type: :utc_datetime]
schema "posts" do
  many_to_many :users, User, join_through: "users_posts", 
  on_replace: :delete
  timestamps()
end
@doc false
def changeset(post, attrs) do
  post
  |> cast_assoc(:users, required: false)
  end
end

Step 3: Create users_posts

This file won’t automatic generated by command, so we have to create by ourselves

defmodule UserPost do
  @primary_key false
  schema "users_posts" do
  belongs_to :user, User
  belongs_to :post, Post
  timestamps()
end
 
@doc false
def changeset(user_post, attrs) do
  user_post
  |> cast(attrs, [:user_id, :post_id])
  |> validate_required([:user_id, :post_id])
  end
end

Step 4: Set migration file

def change do
  create table(:users_posts) do
    add : user_id, references(:users, on_delete: :nothing)
    add : post_id, references(:posts, on_delete: :nothing)
  end
  create unique_index(: users_posts, [:user_id, :post_id])
end

OK, we finished building Many_to_Many association. Now we can start to do CRUD

Many to Many CRUD:

Query

query_cmd = from(
  u in User,
  join: up in UserPost,
  on: u.id == up.user_id,
  join: p in Post,
  on: 
  up.post_id == p.id
    and
  p.id == ^current_post.id,
  where: ^conditions,
  select: [u,p],
)
Repo.one(query_cmd)

Query with other associations

query_cmd = from(
   c in Comment,
   join: p in assoc(c, :post),
   join: up in UserPost,
   on: p.id == up.post_id,
   join: u in User,
   on: up.user_id == u.id and u.id == ^current_user.id,
   where: ^conditions,
   select: [c,f,p]
 )
 Repo.all(query_cmd)

Create Association

def create_association(%User{} = user, %{} = attrs) do
  Ecto.Changeset.change(%Post{}, attrs)
  |> Ecto.Changeset.put_assoc(:users, [user])
  |> Repo.insert
end

Add Association

def add_association(current_post) do
  post_changeset
  |> Ecto.Changeset.put_assoc(:users, current_post.users ++ [user])
  |> Repo.update
end

Remove Association

def remove_association(%User{} = user, %Post{} = post) do
  query_cmd = from(
    up in UserPost,
    where:
    up.user_id == ^user.id
     and
    up.post_id == ^post.id
 )
 Repo.delete_all(query_cmd)
end