Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 38 ยท Ecto Schemas and Queries

Associations, Preloading and Contexts

Model has_many, belongs_to and many_to_many relations in Ecto, avoid N+1 queries with preload, and organize business logic into Phoenix contexts.

Real data is connected: a post has many comments, a comment belongs to one user, a post has many tags and a tag has many posts. Ecto models these relations with associations, and Phoenix organizes the code that uses them into contexts. This lesson covers both: how to declare and load associations without the dreaded N+1 query problem, and how contexts keep your web layer clean.

You should know schemas and migrations and Ecto queries.

The Elixir and Ecto code is read-only here. TryIts model preloading and context boundaries in JavaScript.

Declaring associations

The foreign key lives on the "belongs to" side. In the migration:

ELIXIR
create table(:comments) do
  add :body, :text, null: false
  add :post_id, references(:posts, on_delete: :delete_all), null: false
  add :user_id, references(:users, on_delete: :nilify_all)
  timestamps(type: :utc_datetime)
end

create index(:comments, [:post_id])

on_delete decides what happens when the parent goes: :delete_all removes children, :nilify_all sets the key to nil, :restrict forbids deleting the parent. Index every foreign key you query by.

In the schemas:

ELIXIR
defmodule Blog.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    belongs_to :author, Blog.Accounts.User
    has_many :comments, Blog.Comment
    many_to_many :tags, Blog.Tag, join_through: "posts_tags", on_replace: :delete
    timestamps(type: :utc_datetime)
  end
end

defmodule Blog.Comment do
  use Ecto.Schema

  schema "comments" do
    field :body, :string
    belongs_to :post, Blog.Post
    timestamps(type: :utc_datetime)
  end
end

belongs_to :post adds the field post_id. has_one works like has_many with a single child. many_to_many needs a join table (posts_tags with post_id and tag_id).

Associations are not loaded automatically

ELIXIR
post = Repo.get!(Post, 1)
post.comments
# #Ecto.Association.NotLoaded<association :comments is not loaded>

Ecto never fetches related rows behind your back, because hidden queries are how apps become slow. Load them explicitly.

Preload

ELIXIR
post = Post |> Repo.get!(1) |> Repo.preload([:comments, author: :profile])

posts = Post |> where([p], p.published) |> preload(:author) |> Repo.all()

Repo.preload runs one extra query per association, not one per row. For a list of 50 posts, preload(:comments) makes 2 queries total (posts, then all their comments with WHERE post_id IN (...)).

The N+1 problem

The bad version loads comments inside a loop:

ELIXIR
for post <- Repo.all(Post) do
  Repo.all(from c in Comment, where: c.post_id == ^post.id)    # one query per post
end

With 100 posts this issues 101 queries. The same page with preload(:comments) issues 2. Look for N+1 in LiveDashboard or in the dev log, where repeated SELECT ... WHERE post_id = $1 lines are the sign. Preload in the context function that returns the list, not in templates.

You can also join and preload in one query when you need to filter by the association: from p in Post, join: c in assoc(p, :comments), where: c.approved, preload: [comments: c].

Creating associated data

ELIXIR
# build through the association: sets post_id for you
comment =
  post
  |> Ecto.build_assoc(:comments, body: "Nice!")
  |> Repo.insert!()

# or cast the foreign key from trusted code, not from user params
%Comment{}
|> Comment.changeset(attrs)
|> Ecto.Changeset.put_assoc(:post, post)
|> Repo.insert()

Set ownership (user_id, post_id) from the current user and URL, never from form fields a user could tamper with.

Contexts

A context is a module that groups related functionality behind a clear public API. Blog.Posts owns posts and comments; Blog.Accounts owns users. The web layer calls context functions and never touches Repo or builds queries itself:

ELIXIR
defmodule Blog.Posts do
  import Ecto.Query
  alias Blog.{Repo, Post, Comment}

  def list_posts do
    Post |> order_by(desc: :inserted_at) |> preload(:author) |> Repo.all()
  end

  def get_post!(id), do: Post |> preload([:author, comments: :user]) |> Repo.get!(id)

  def create_post(%User{} = author, attrs) do
    %Post{author_id: author.id}
    |> Post.changeset(attrs)
    |> Repo.insert()
  end

  def add_comment(%Post{} = post, %User{} = user, attrs) do
    %Comment{post_id: post.id, user_id: user.id}
    |> Comment.changeset(attrs)
    |> Repo.insert()
  end

  def change_post(%Post{} = post, attrs \\ %{}), do: Post.changeset(post, attrs)
end

Benefits: controllers and LiveViews stay thin; rules live in one place; tests call plain functions; you can change the database layout without touching the web layer. Name functions after domain actions (add_comment, publish_post) and not after SQL. Split contexts by business area, not by table: a context may own several schemas. When one context needs data from another, call its public functions rather than reaching into its schemas.

Transactions with Ecto.Multi

When several writes must succeed or fail together, use Ecto.Multi:

ELIXIR
Ecto.Multi.new()
|> Ecto.Multi.insert(:post, Post.changeset(%Post{author_id: user.id}, attrs))
|> Ecto.Multi.update_all(:counter, from(u in User, where: u.id == ^user.id), inc: [post_count: 1])
|> Repo.transaction()
# {:ok, %{post: post, counter: {1, nil}}} | {:error, failed_step, value, changes_so_far}

If any step fails, everything rolls back.

Preload and N+1, modeled

JS Counting queries: N+1 versus preload

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Both return identical data, but one needs a query per post. Preload groups the lookups into a single IN query and stitches the rows together in memory.

A context boundary, modeled

JS Thin controller, domain-named context

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The controller only translates results into HTTP; ownership (author_id) comes from the current user, not the params.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Where does the foreign key column live in a has_many / belongs_to pair?

    The child row stores the parent's id, so belongs_to owns the foreign key.
  2. 2. What does Repo.preload(posts, :comments) do for 50 posts?

    Preload batches the lookup into one query with IN, avoiding N+1.
  3. 3. Why does Ecto not load associations automatically?

    Explicit loading keeps the number and cost of queries visible and under your control.
  4. 4. What is a Phoenix context?

    Contexts hold domain logic and are the only layer the web code should call for data.
  5. 5. Where should user_id of a new comment come from?

    Ownership must come from server-side trusted data, never from fields users can edit.

Summary

  • Declare belongs_to, has_many, has_one and many_to_many; the foreign key lives on the child, and on_delete governs parent removal.
  • Associations load only when asked: use preload to avoid N+1 queries.
  • Contexts such as Blog.Posts hide Repo and queries behind domain-named functions and keep controllers thin.
  • Use Ecto.Multi for multi-step writes that must succeed together, and set ownership from the current user.

Next: LiveView Lifecycle: Mount, Render and Assigns