Ecto Relationships Basic: Mastering Data Associations

Ecto Relationships Basic: Mastering Data Associations

With Elixir 1.12 and Phoenix 1.6 bringing significant improvements to the ecosystem, understanding Ecto relationships has become more crucial than ever. Proper data modeling is the foundation of any robust application.

Why Ecto Relationships Matter

Traditional ORMs hide complexity behind magic methods. Ecto takes a different approach—it’s explicit, composable, and gives you complete control over your database interactions.

# Instead of magic methods like user.posts.create()
# You get explicit, composable queries
user
|> Ecto.build_assoc(:posts)
|> Post.changeset(post_params)
|> Repo.insert()

This explicitness leads to better performance, clearer debugging, and more maintainable code.

The Four Essential Relationships

1. Has One

A user has one profile:

# Schema definition
defmodule MyApp.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field :email, :string
    field :name, :string
    
    has_one :profile, MyApp.Profile
    
    timestamps()
  end
end

defmodule MyApp.Profile do
  use Ecto.Schema
  import Ecto.Changeset

  schema "profiles" do
    field :bio, :string
    field :avatar_url, :string
    field :website, :string
    
    belongs_to :user, MyApp.User
    
    timestamps()
  end
end

Migration:

defmodule MyApp.Repo.Migrations.CreateProfiles do
  use Ecto.Migration

  def change do
    create table(:profiles) do
      add :bio, :text
      add :avatar_url, :string
      add :website, :string
      add :user_id, references(:users, on_delete: :delete_all), null: false

      timestamps()
    end

    create unique_index(:profiles, [:user_id])
  end
end

2. Has Many

A user has many posts:

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :email, :string
    field :name, :string
    
    has_many :posts, MyApp.Post
    
    timestamps()
  end
end

defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    field :body, :text
    field :published_at, :utc_datetime
    
    belongs_to :user, MyApp.User
    
    timestamps()
  end
end

Migration:

defmodule MyApp.Repo.Migrations.CreatePosts do
  use Ecto.Migration

  def change do
    create table(:posts) do
      add :title, :string, null: false
      add :body, :text
      add :published_at, :utc_datetime
      add :user_id, references(:users, on_delete: :delete_all), null: false

      timestamps()
    end

    create index(:posts, [:user_id])
    create index(:posts, [:published_at])
  end
end

3. Belongs To

Already shown above, but here’s the key insight:

# The belongs_to side holds the foreign key
defmodule MyApp.Post do
  schema "posts" do
    # This creates a user_id field automatically
    belongs_to :user, MyApp.User
    
    # Explicit foreign key (optional)
    belongs_to :category, MyApp.Category, foreign_key: :cat_id
  end
end

4. Many to Many

Posts have many tags, tags have many posts:

defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    field :body, :text
    
    many_to_many :tags, MyApp.Tag, join_through: "posts_tags"
    
    timestamps()
  end
end

defmodule MyApp.Tag do
  use Ecto.Schema

  schema "tags" do
    field :name, :string
    field :color, :string
    
    many_to_many :posts, MyApp.Post, join_through: "posts_tags"
    
    timestamps()
  end
end

Join Table Migration:

defmodule MyApp.Repo.Migrations.CreatePostsTags do
  use Ecto.Migration

  def change do
    create table(:posts_tags, primary_key: false) do
      add :post_id, references(:posts, on_delete: :delete_all), null: false
      add :tag_id, references(:tags, on_delete: :delete_all), null: false
    end

    create unique_index(:posts_tags, [:post_id, :tag_id])
    create index(:posts_tags, [:tag_id])
  end
end

Advanced: Many to Many with Join Schema

For additional data on the relationship:

defmodule MyApp.PostTag do
  use Ecto.Schema

  schema "posts_tags" do
    field :featured, :boolean, default: false
    field :added_by, :string
    
    belongs_to :post, MyApp.Post
    belongs_to :tag, MyApp.Tag
    
    timestamps()
  end
end

# Update the schemas
defmodule MyApp.Post do
  schema "posts" do
    # ...
    many_to_many :tags, MyApp.Tag, join_through: MyApp.PostTag
    has_many :post_tags, MyApp.PostTag
  end
end

Querying Relationships

Basic Preloading

# Load user with posts
user = Repo.get!(User, 1) |> Repo.preload(:posts)

# Load posts with user and tags
posts = Repo.all(Post) |> Repo.preload([:user, :tags])

# Nested preloading
users = Repo.all(User) |> Repo.preload([posts: :tags])

Custom Preload Queries

# Only published posts
users = 
  Repo.all(User)
  |> Repo.preload([posts: from(p in Post, where: not is_nil(p.published_at))])

# Recent posts with user
recent_posts_query = from p in Post, 
  where: p.inserted_at > ago(7, "day"),
  order_by: [desc: p.inserted_at]

users = 
  Repo.all(User)
  |> Repo.preload([posts: recent_posts_query])

Join Queries

# Users who have published posts
active_users = 
  from u in User,
    join: p in assoc(u, :posts),
    where: not is_nil(p.published_at),
    distinct: true,
    select: u

# Posts with user information
posts_with_users = 
  from p in Post,
    join: u in assoc(p, :user),
    select: {p, u}

# Count posts per user
user_post_counts = 
  from u in User,
    left_join: p in assoc(u, :posts),
    group_by: u.id,
    select: {u, count(p.id)}

Working with Associations

Creating Associated Records

# Build association
user = Repo.get!(User, 1)
post = Ecto.build_assoc(user, :posts, %{title: "New Post", body: "Content"})
{:ok, created_post} = Repo.insert(post)

# Create with association
{:ok, user} = 
  User.changeset(%User{}, user_params)
  |> Ecto.Changeset.put_assoc(:profile, profile_params)
  |> Repo.insert()

Updating Associations

# Replace associations
changeset = 
  post
  |> Repo.preload(:tags)
  |> Post.changeset(post_params)
  |> Ecto.Changeset.put_assoc(:tags, new_tags)

{:ok, updated_post} = Repo.update(changeset)

# Add to existing associations
new_tag = %Tag{name: "elixir"}
updated_tags = post.tags ++ [new_tag]

changeset = 
  post
  |> Post.changeset(%{})
  |> Ecto.Changeset.put_assoc(:tags, updated_tags)

Deleting Associations

# Remove specific associations
remaining_tags = Enum.reject(post.tags, &(&1.id == tag_to_remove.id))

changeset = 
  post
  |> Post.changeset(%{})
  |> Ecto.Changeset.put_assoc(:tags, remaining_tags)

{:ok, updated_post} = Repo.update(changeset)

# Clear all associations
changeset = 
  post
  |> Post.changeset(%{})
  |> Ecto.Changeset.put_assoc(:tags, [])

Performance Optimization

N+1 Query Prevention

# ❌ N+1 problem
users = Repo.all(User)
Enum.each(users, fn user ->
  posts = Repo.all(assoc(user, :posts))  # Separate query for each user!
  IO.inspect(length(posts))
end)

# ✅ Efficient preloading
users = Repo.all(User) |> Repo.preload(:posts)
Enum.each(users, fn user ->
  IO.inspect(length(user.posts))  # No additional queries
end)

Selective Loading

# Only load what you need
posts = 
  from p in Post,
    join: u in assoc(p, :user),
    select: %{
      id: p.id,
      title: p.title,
      author_name: u.name,
      created_at: p.inserted_at
    }

Counting Efficiently

# ❌ Loading all records to count
user_post_count = length(user.posts)

# ✅ Database-level counting
user_post_count = 
  from p in Post,
    where: p.user_id == ^user.id,
    select: count(p.id)
  |> Repo.one()

Real-World Example: Blog System

defmodule MyApp.Blog do
  alias MyApp.{Repo, User, Post, Tag, Comment}
  import Ecto.Query

  def get_user_with_stats(user_id) do
    from u in User,
      where: u.id == ^user_id,
      left_join: p in assoc(u, :posts),
      left_join: c in assoc(p, :comments),
      group_by: u.id,
      select: %{
        user: u,
        post_count: count(p.id, :distinct),
        comment_count: count(c.id)
      }
    |> Repo.one()
  end

  def get_post_with_everything(post_id) do
    Post
    |> where(id: ^post_id)
    |> preload([
      :user,
      :tags,
      comments: :user
    ])
    |> Repo.one()
  end

  def get_popular_posts(limit \\ 10) do
    from p in Post,
      left_join: c in assoc(p, :comments),
      group_by: p.id,
      order_by: [desc: count(c.id)],
      limit: ^limit,
      preload: [:user, :tags],
      select: p
    |> Repo.all()
  end

  def create_post_with_tags(user, post_params, tag_names) do
    tags = get_or_create_tags(tag_names)
    
    user
    |> Ecto.build_assoc(:posts)
    |> Post.changeset(post_params)
    |> Ecto.Changeset.put_assoc(:tags, tags)
    |> Repo.insert()
  end

  defp get_or_create_tags(tag_names) do
    existing_tags = 
      from t in Tag,
        where: t.name in ^tag_names,
        select: t
      |> Repo.all()

    existing_names = Enum.map(existing_tags, & &1.name)
    new_names = tag_names -- existing_names

    new_tags = 
      Enum.map(new_names, fn name ->
        %Tag{name: name}
        |> Repo.insert!()
      end)

    existing_tags ++ new_tags
  end
end

Common Patterns and Pitfalls

Pattern: Soft Deletes with Associations

defmodule MyApp.Post do
  schema "posts" do
    field :deleted_at, :utc_datetime
    has_many :comments, MyApp.Comment, where: [deleted_at: nil]
  end

  def published_posts(query \\ __MODULE__) do
    from p in query,
      where: is_nil(p.deleted_at) and not is_nil(p.published_at)
  end
end

Pitfall: Forgetting Indexes

# Always index foreign keys
create index(:posts, [:user_id])
create index(:comments, [:post_id])

# Index frequently queried combinations
create index(:posts, [:user_id, :published_at])
create index(:posts, [:published_at, :inserted_at])

Pattern: Polymorphic Associations

defmodule MyApp.Comment do
  schema "comments" do
    field :body, :text
    field :commentable_type, :string
    field :commentable_id, :integer
    
    belongs_to :user, MyApp.User
  end
end

# Usage
def get_comments_for(commentable) do
  type = commentable.__struct__ |> Module.split() |> List.last()
  
  from c in Comment,
    where: c.commentable_type == ^type and c.commentable_id == ^commentable.id,
    preload: :user
  |> Repo.all()
end

Testing Relationships

defmodule MyApp.BlogTest do
  use MyApp.DataCase
  alias MyApp.{User, Post, Tag}

  test "creating a post with tags" do
    user = insert(:user)
    tag1 = insert(:tag, name: "elixir")
    tag2 = insert(:tag, name: "phoenix")

    {:ok, post} = 
      user
      |> Ecto.build_assoc(:posts)
      |> Post.changeset(%{title: "Test", body: "Content"})
      |> Ecto.Changeset.put_assoc(:tags, [tag1, tag2])
      |> Repo.insert()

    post = Repo.preload(post, :tags)
    assert length(post.tags) == 2
    assert Enum.any?(post.tags, &(&1.name == "elixir"))
  end

  test "deleting user cascades to posts" do
    user = insert(:user)
    post = insert(:post, user: user)

    Repo.delete!(user)
    assert Repo.get(Post, post.id) == nil
  end
end

Understanding these relationship patterns will make your Phoenix applications more robust and maintainable. The key is being explicit about your data needs and leveraging Ecto’s composable query system.

---

Next: Dive into real-time communication with Phoenix WebSockets and Channels.