Many-to-Many Relationships in Elixir with Ecto: A Complete Guide

Today we are going to talk about a very common scenario which we face when designing any application i.e. Many-Many relationship between the tables. As I have seen it’s a very common issue arise working with Ecto, new users of elixir face a bit of difficulty while working with it. So here is how to implement many-to-many relationships in Elixir

It’s going to be a quick & crisp read to understand the usage of the Ecto for this.

Setting Up Our Application

Let’s create a new app with our classic way:

mix phx.new learn_many --no-html --no-assets
cd learn_manyCode language: Elixir (elixir)

Create the database:

mix ecto.createCode language: CSS (css)

Designing Our Domain Model

For this tutorial, we’ll build a health tracking application with the following relationships:

• User → Categories → SubCategories
• User → HealthData

So a user can follow multiple categories and categories can have multiple sub_categories but user can only have one health related data.

In short, our relationships are:

• 1:1 mapping between User and HealthData
• N:N mapping between User and Categories
• N:N mapping between Categories and SubCategories

Generating Our Core Schemas

Let’s start by generating our main entities:

# Generate Users
mix phx.gen.context Accounts User users name:string email:string:unique age:integer
# Generate Categories  
mix phx.gen.context Content Category categories name:string description:text
# Generate SubCategories
mix phx.gen.context Content SubCategory sub_categories name:string description:text
# Generate HealthData (1:1 with User)
mix phx.gen.context Health HealthData health_data user_id:references:users weight:float height:float blood_type:stringCode language: Elixir (elixir)

Now let’s generate the junction tables for our many-to-many relationships:

# Generate the mapping schema for users <-> categories
mix phx.gen.context Registration UserCategory user_categories category_id:references:categories user_id:references:users

# Generate the mapping schema for categories <-> sub_categories  
mix phx.gen.context Content CategorySubCategory category_sub_categories category_id:references:categories sub_category_id:references:sub_categoriesCode language: Elixir (elixir)

Understanding the Junction Table Schema

Let’s examine the UserCategory module that was generated and see how we need to modify it:

defmodule LearnMany.Registration.UserCategory do
  use Ecto.Schema
  import Ecto.Changeset

  alias LearnMany.Accounts.User
  alias LearnMany.Content.Category

  @required [:category_id, :user_id]
  @already_exists "ALREADY_EXISTS"

  # Important: Set primary_key to false for junction tables
  @primary_key false
  schema "user_categories" do
    belongs_to :user, User, primary_key: true
    belongs_to :category, Category, primary_key: true

    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(user_category, attrs) do
    user_category
    |> cast(attrs, @required)
    |> validate_required(@required)
    |> foreign_key_constraint(:user_id)
    |> foreign_key_constraint(:category_id)
    |> unique_constraint(
      [:user_id, :category_id],
      name: :user_id_category_id_unique_index,
      message: @already_exists
    )
  end

  def create_user_category(user_id, category_id) do
    %__MODULE__{}
    |> changeset(%{user_id: user_id, category_id: category_id})
    |> LearnMany.Repo.insert()
  end

  def map_user_categories(user, categories) when is_list(categories) do
    user_categories_existing = user.categories || []

    user
    |> LearnMany.Repo.preload(:categories)
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:categories, user_categories_existing ++ categories)
    |> LearnMany.Repo.update()
  end

  def map_user_categories(user, category) do
    map_user_categories(user, [category])
  end
endCode language: Elixir (elixir)

Key Points in the Junction Table:

1. @primary_key false: Junction tables typically don’t need a separate ID column
2. belongs_to with primary_key: true: Both foreign keys serve as composite primary key
3. Unique constraint: Prevents duplicate relationships
4. Foreign key constraints: Ensures referential integrity

Setting Up Main Schema Associations

Now let’s update our main schemas to include the many-to-many relationships:

User Schema

defmodule LearnMany.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset

  alias LearnMany.Content.Category
  alias LearnMany.Health.HealthData
  alias LearnMany.Registration.UserCategory

  schema "users" do
    field :name, :string
    field :email, :string
    field :age, :integer

    # 1:1 relationship with HealthData
    has_one :health_data, HealthData

    # Many-to-many relationship with Categories
    many_to_many :categories, Category,
      join_through: UserCategory,
      join_keys: [user_id: :id, category_id: :id]

    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:name, :email, :age])
    |> validate_required([:name, :email])
    |> validate_format(:email, ~r/@/)
    |> unique_constraint(:email)
  end

  def changeset_with_categories(user, attrs, categories \\ []) do
    user
    |> changeset(attrs)
    |> put_assoc(:categories, categories)
  end
endCode language: CSS (css)

Category Schema

defmodule LearnMany.Content.Category do
  use Ecto.Schema
  import Ecto.Changeset

  alias LearnMany.Accounts.User
  alias LearnMany.Content.SubCategory
  alias LearnMany.Registration.UserCategory
  alias LearnMany.Content.CategorySubCategory

  schema "categories" do
    field :name, :string
    field :description, :string

    # Many-to-many relationship with Users
    many_to_many :users, User,
      join_through: UserCategory,
      join_keys: [category_id: :id, user_id: :id]

    # Many-to-many relationship with SubCategories
    many_to_many :sub_categories, SubCategory,
      join_through: CategorySubCategory,
      join_keys: [category_id: :id, sub_category_id: :id]

    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(category, attrs) do
    category
    |> cast(attrs, [:name, :description])
    |> validate_required([:name])
    |> unique_constraint(:name)
  end
endCode language: Elixir (elixir)

SubCategory Schema

defmodule LearnMany.Content.SubCategory do
  use Ecto.Schema
  import Ecto.Changeset

  alias LearnMany.Content.Category
  alias LearnMany.Content.CategorySubCategory

  schema "sub_categories" do
    field :name, :string
    field :description, :string

    # Many-to-many relationship with Categories
    many_to_many :categories, Category,
      join_through: CategorySubCategory,
      join_keys: [sub_category_id: :id, category_id: :id]

    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(sub_category, attrs) do
    sub_category
    |> cast(attrs, [:name, :description])
    |> validate_required([:name])
  end
endCode language: Elixir (elixir)

HealthData Schema (1:1 Relationship)

defmodule LearnMany.Health.HealthData do
  use Ecto.Schema
  import Ecto.Changeset

  alias LearnMany.Accounts.User

  schema "health_data" do
    field :weight, :float
    field :height, :float
    field :blood_type, :string

    # 1:1 relationship with User
    belongs_to :user, User

    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(health_data, attrs) do
    health_data
    |> cast(attrs, [:weight, :height, :blood_type, :user_id])
    |> validate_required([:user_id])
    |> validate_number(:weight, greater_than: 0)
    |> validate_number(:height, greater_than: 0)
    |> validate_inclusion(:blood_type, ["A+", "A-", "B+", "B-", "AB+", "AB-", "O+", "O-"])
    |> unique_constraint(:user_id)
    |> foreign_key_constraint(:user_id)
  end
endCode language: Elixir (elixir)

Database Migration Updates

Make sure your migrations include the necessary indexes and constraints:

# In the user_categories migration
defmodule LearnMany.Repo.Migrations.CreateUserCategories do
  use Ecto.Migration
  
  def change do
    create table(:user_categories, primary_key: false) do
      add :user_id, references(:users, on_delete: :delete_all), null: false
      add :category_id, references(:categories, on_delete: :delete_all), null: false
      timestamps(type: :utc_datetime)
    end
    create unique_index(:user_categories, [:user_id, :category_id], 
           name: :user_id_category_id_unique_index)
    create index(:user_categories, [:user_id])
    create index(:user_categories, [:category_id])
  end
endCode language: Elixir (elixir)

Working with Many-to-Many Relationships

Now let’s see how to use these relationships in practice:

Creating and Managing Associations

defmodule LearnMany.UserManager do
  alias LearnMany.{Repo, Accounts.User, Content.Category, Health.HealthData}
  import Ecto.Query

  def create_user_with_categories(user_attrs, category_ids \\ []) do
    categories = Repo.all(from c in Category, where: c.id in ^category_ids)
    
    %User{}
    |> User.changeset_with_categories(user_attrs, categories)
    |> Repo.insert()
  end

  def add_categories_to_user(user_id, category_ids) when is_list(category_ids) do
    user = Repo.get!(User, user_id) |> Repo.preload(:categories)
    new_categories = Repo.all(from c in Category, where: c.id in ^category_ids)
    
    existing_category_ids = Enum.map(user.categories, & &1.id)
    categories_to_add = Enum.reject(new_categories, & &1.id in existing_category_ids)
    
    user
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:categories, user.categories ++ categories_to_add)
    |> Repo.update()
  end

  def remove_categories_from_user(user_id, category_ids) when is_list(category_ids) do
    user = Repo.get!(User, user_id) |> Repo.preload(:categories)
    
    remaining_categories = Enum.reject(user.categories, & &1.id in category_ids)
    
    user
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:categories, remaining_categories)
    |> Repo.update()
  end

  def get_user_with_all_associations(user_id) do
    Repo.get!(User, user_id)
    |> Repo.preload([
      :health_data,
      categories: [sub_categories: []]
    ])
  end

  def create_health_data_for_user(user_id, health_attrs) do
    health_attrs
    |> Map.put(:user_id, user_id)
    |> then(&HealthData.changeset(%HealthData{}, &1))
    |> Repo.insert()
  end
endCode language: Elixir (elixir)

Querying Many-to-Many Relationships

defmodule LearnMany.Queries do
  alias LearnMany.{Repo, Accounts.User, Content.Category}
  import Ecto.Query

  # Find users who follow specific categories
  def users_following_categories(category_names) do
    from(u in User,
      join: uc in "user_categories", on: u.id == uc.user_id,
      join: c in Category, on: c.id == uc.category_id,
      where: c.name in ^category_names,
      distinct: u.id,
      select: u
    )
    |> Repo.all()
  end

  # Find categories with most followers
  def popular_categories(limit \\ 10) do
    from(c in Category,
      left_join: uc in "user_categories", on: c.id == uc.category_id,
      group_by: c.id,
      order_by: [desc: count(uc.user_id)],
      limit: ^limit,
      select: {c, count(uc.user_id)}
    )
    |> Repo.all()
  end

  # Find users with health data in specific categories
  def users_with_health_data_in_categories(category_names) do
    from(u in User,
      join: hd in assoc(u, :health_data),
      join: uc in "user_categories", on: u.id == uc.user_id,
      join: c in Category, on: c.id == uc.category_id,
      where: c.name in ^category_names and not is_nil(hd.id),
      distinct: u.id,
      preload: [:health_data, :categories]
    )
    |> Repo.all()
  end

  # Get category statistics
  def category_stats do
    from(c in Category,
      left_join: uc in "user_categories", on: c.id == uc.category_id,
      left_join: csc in "category_sub_categories", on: c.id == csc.category_id,
      group_by: c.id,
      select: %{
        category: c,
        user_count: count(uc.user_id, :distinct),
        sub_category_count: count(csc.sub_category_id, :distinct)
      }
    )
    |> Repo.all()
  end
endCode language: Elixir (elixir)

Testing Your Many-to-Many Relationships

Here’s how you can test these relationships:

# In IEx (iex -S mix)

# Create some sample data
alias LearnMany.{UserManager, Repo, Content.Category, Content.SubCategory}
# Create categories
{:ok, fitness} = Repo.insert(%Category{name: "Fitness", description: "Physical health and exercise"})
{:ok, nutrition} = Repo.insert(%Category{name: "Nutrition", description: "Diet and nutrition"})
{:ok, mental_health} = Repo.insert(%Category{name: "Mental Health", description: "Mental wellness"})
# Create subcategories
{:ok, cardio} = Repo.insert(%SubCategory{name: "Cardio", description: "Cardiovascular exercises"})
{:ok, strength} = Repo.insert(%SubCategory{name: "Strength Training", description: "Weight lifting and resistance"})
# Associate categories with subcategories
fitness
|> Ecto.Changeset.change()
|> Ecto.Changeset.put_assoc(:sub_categories, [cardio, strength])
|> Repo.update()
# Create a user with categories
{:ok, user} = UserManager.create_user_with_categories(
  %{name: "John Doe", email: "john@example.com", age: 30},
  [fitness.id, nutrition.id]
)
# Add health data
{:ok, _health_data} = UserManager.create_health_data_for_user(
  user.id,
  %{weight: 75.5, height: 180.0, blood_type: "O+"}
)
# Get user with all associations
complete_user = UserManager.get_user_with_all_associations(user.id)
IO.inspect(complete_user, label: "Complete User Data")Code language: Elixir (elixir)

Common Pitfalls and Best Practices

1. Always Preload Associations

# ❌ This will cause N+1 queries
users = Repo.all(User)
Enum.each(users, fn user -> 
  IO.inspect(user.categories) # This hits the database each time
end)

# ✅ Preload to avoid N+1 queries  
users = User |> Repo.all() |> Repo.preload(:categories)
Enum.each(users, fn user ->
  IO.inspect(user.categories) # No additional database hits
end)Code language: Elixir (elixir)

2. Use Transactions for Complex Operations

def create_user_with_complete_setup(user_attrs, category_ids, health_attrs) do
  Repo.transaction(fn ->
    with {:ok, user} <- UserManager.create_user_with_categories(user_attrs, category_ids),
         {:ok, _health_data} <- UserManager.create_health_data_for_user(user.id, health_attrs) do
      user
    else
      {:error, changeset} -> Repo.rollback(changeset)
    end
  end)
endCode language: Elixir (elixir)

3. Handle Unique Constraints Gracefully

def safe_add_category_to_user(user_id, category_id) do
  case UserManager.add_categories_to_user(user_id, [category_id]) do
    {:ok, user} -> {:ok, user}
    {:error, %Ecto.Changeset{errors: errors}} ->
      case Keyword.get(errors, :categories) do
        {"ALREADY_EXISTS", _} -> {:ok, :already_exists}
        _ -> {:error, "Failed to add category"}
      end
  end
endCode language: JavaScript (javascript)

Performance Considerations

Indexing Strategy

Make sure your junction tables have proper indexes:

# In migration
create index(:user_categories, [:user_id])
create index(:user_categories, [:category_id]) 
create unique_index(:user_categories, [:user_id, :category_id])Code language: Elixir (elixir)

Batch Operations

For bulk operations, consider using Repo.insert_all/3:

def bulk_assign_categories_to_users(user_ids, category_ids) do
  timestamp = DateTime.utc_now() |> DateTime.truncate(:second)
  
  user_categories = 
    for user_id <- user_ids,
        category_id <- category_ids do
      %{
        user_id: user_id,
        category_id: category_id,
        inserted_at: timestamp,
        updated_at: timestamp
      }
    endCode language: JavaScript (javascript)

 Repo.insert_all("user_categories", user_categories, 
    on_conflict: :nothing,
    conflict_target: [:user_id, :category_id]
  )
endCode language: CSS (css)

Conclusion

Many-to-many relationships in Ecto require understanding of junction tables, proper schema design, and careful attention to performance. Key takeaways:

1. Use junction tables with composite primary keys for many-to-many relationships
2. Set up proper associations in all related schemas
3. Always preload associations to avoid N+1 queries
4. Use transactions for complex multi-table operations
5. Index your junction tables properly for performance
6. Handle unique constraints gracefully in your application logic

With these patterns, you’ll be able to handle complex many-to-many relationships efficiently in your Elixir applications. The key is to understand that Ecto gives you powerful tools, but you need to use them thoughtfully to build maintainable and performant applications.

Remember: Many-to-many relationships are powerful but can be complex. Start simple, test thoroughly, and optimize based on your actual usage patterns.

If you liked the blog, please consider buying me a coffee 😝 https://buymeacoffee.com/y316nitka Thank You!

---