How to query with joins, group_by, and pipe-style Ecto

Write readable Ecto queries using the pipe operator with joins, group_by, having, and aggregates in Phoenix applications.

Ecto supports two query styles: the keyword style (using from) and the pipe style (chasing functions with |>). The pipe style is more readable for complex queries because each step is on its own line and the data flows left-to-right. This guide focuses on pipe-style queries with joins, grouping, and aggregation.

Setup

import Ecto.Query
alias MyApp.Repo
alias MyApp.Blog.{Post, Comment, Category}
alias MyApp.Accounts.User

Why pipe style?

Compare the same query in both styles:

# Keyword style — gets hard to read as complexity grows
from p in Post,
  join: c in Comment, on: c.post_id == p.id,
  join: u in User, on: p.author_id == u.id,
  where: p.published == true and c.approved == true,
  group_by: [u.name, p.title],
  having: count(c.id) > 2,
  order_by: [desc: count(c.id)],
  select: %{author: u.name, post: p.title, comment_count: count(c.id)}

# Pipe style — each step is a clear transformation
Post
|> join(:inner, [p], c in Comment, on: c.post_id == p.id)
|> join(:inner, [p, c], u in User, on: p.author_id == u.id)
|> where([p, c, u], p.published == true and c.approved == true)
|> group_by([u, p], [u.name, p.title])
|> having([p, c], count(c.id) > 2)
|> order_by([p, c], desc: count(c.id))
|> select([p, c, u], %{author: u.name, post: p.title, comment_count: count(c.id)})
|> Repo.all()

The pipe style reads top-to-bottom, each line is one operation, and you can comment out any step to debug.

Joins

Inner join

Returns only rows that have matches in both tables:

# Posts that have comments
Post
|> join(:inner, [p], c in Comment, on: c.post_id == p.id)
|> select([p, c], {p.title, c.body})
|> Repo.all()

Left join

Returns all rows from the left table, with nil for unmatched right-side columns:

# All posts, including those without comments
Post
|> join(:left, [p], c in Comment, on: c.post_id == p.id)
|> select([p, c], {p.title, c.body})
|> Repo.all()

Multiple joins

Chain joins for queries across three or more tables:

# Posts → Comments → Users (comment authors)
Post
|> join(:inner, [p], c in Comment, on: c.post_id == p.id)
|> join(:inner, [p, c], u in User, on: c.author_id == u.id)
|> select([p, c, u], {p.title, c.body, u.name})
|> Repo.all()

Join with assoc

Use assoc/2 for cleaner join syntax when you have schema associations defined:

# Post has_many :comments, Comment belongs_to :post
Post
|> join(:inner, [p], c in assoc(p, :comments))
|> select([p, c], {p.title, c.body})
|> Repo.all()

# Join through multiple associations
Post
|> join(:inner, [p], c in assoc(p, :comments))
|> join(:inner, [p, c], u in assoc(c, :author))
|> select([p, c, u], {p.title, u.name, c.body})
|> Repo.all()

Self join

# Find users who share a category with user 1
from u in User,
  join: u2 in User,
  on: u2.category_id == u.category_id and u2.id != u.id,
  where: u.id == 1

Preloading with joins

Joins + preload efficiently load associations in a single query:

# Load posts with their comments
Post
|> join(:inner, [p], c in assoc(p, :comments))
|> where([c], c.approved == true)
|> preload([p, c], comments: c)
|> Repo.all()

# Nested preload: posts → comments → comment author
Post
|> join(:inner, [p], c in assoc(p, :comments))
|> join(:inner, [p, c], u in assoc(c, :author))
|> where([c], c.approved == true)
|> preload([p, c, u], comments: {c, author: u})
|> Repo.all()

Group by

Basic grouping

# Count posts per author
Post
|> group_by([p], p.author_id)
|> select([p], %{author_id: p.author_id, count: count(p.id)})
|> Repo.all()

# Count comments per post
Comment
|> group_by([c], c.post_id)
|> select([c], %{post_id: c.post_id, count: count(c.id)})
|> Repo.all()

Group by with join

# Posts per category with category name
Post
|> join(:inner, [p], cat in Category, on: p.category_id == cat.id)
|> group_by([p, cat], cat.name)
|> select([p, cat], %{category: cat.name, post_count: count(p.id)})
|> Repo.all()

Multiple group by fields

# Group by author and published status
Post
|> group_by([p], [p.author_id, p.published])
|> select([p], %{author_id: p.author_id, published: p.published, count: count(p.id)})
|> Repo.all()

Group by with date truncation

# Posts grouped by month
Post
|> group_by([p], fragment("date_trunc('month', ?)", p.inserted_at))
|> select([p], %{month: fragment("date_trunc('month', ?)", p.inserted_at), count: count(p.id)})
|> Repo.all()

Aggregates

All standard SQL aggregates are available as functions in Ecto queries:

# count
Post |> select([p], count(p.id)) |> Repo.one()

# count distinct
Post |> select([p], count(p.author_id, :distinct)) |> Repo.one()

# average
Post |> select([p], avg(p.views)) |> Repo.one()

# sum
Post |> select([p], sum(p.views)) |> Repo.one()

# min and max
Post |> select([p], max(p.inserted_at)) |> Repo.one()
Post |> select([p], min(p.inserted_at)) |> Repo.one()

Multiple aggregates in one query

Post
|> where([p], p.published == true)
|> select([p], %{
  total: count(p.id),
  avg_views: avg(p.views),
  max_views: max(p.views),
  total_views: sum(p.views)
})
|> Repo.one()

Having

having filters groups — like where but after aggregation:

# Authors with more than 5 posts
Post
|> group_by([p], p.author_id)
|> having([p], count(p.id) > 5)
|> select([p], %{author_id: p.author_id, post_count: count(p.id)})
|> Repo.all()

# Categories with high average views
Post
|> join(:inner, [p], cat in Category, on: p.category_id == cat.id)
|> group_by([p, cat], cat.name)
|> having([p], avg(p.views) > 100)
|> select([p, cat], %{category: cat.name, avg_views: avg(p.views)})
|> Repo.all()

Full pipe-style query examples

Top 10 authors by comment count

User
|> join(:inner, [u], c in Comment, on: c.author_id == u.id)
|> group_by([u], [u.id, u.name])
|> select([u, c], %{author: u.name, comment_count: count(c.id)})
|> order_by([u, c], desc: count(c.id))
|> limit(10)
|> Repo.all()

Posts with comment counts, including zero-comment posts

Post
|> join(:left, [p], c in Comment, on: c.post_id == p.id)
|> group_by([p], [p.id, p.title])
|> select([p, c], %{title: p.title, comment_count: count(c.id)})
|> order_by([p], desc: p.inserted_at)
|> Repo.all()

Monthly post statistics for a given year

Post
|> where([p], fragment("extract(year from ?) = ?", p.inserted_at, ^2025))
|> group_by([p], fragment("date_trunc('month', ?)", p.inserted_at))
|> select([p], %{
  month: fragment("date_trunc('month', ?)", p.inserted_at),
  post_count: count(p.id),
  avg_views: avg(p.views),
  total_views: sum(p.views)
})
|> order_by([p], asc: fragment("date_trunc('month', ?)", p.inserted_at))
|> Repo.all()

Search with join and relevance scoring

Post
|> join(:left, [p], c in Comment, on: c.post_id == p.id)
|> where([p], ilike(p.title, ^"%#{search_term}%"))
|> group_by([p], p.id)
|> select([p, c], %{
  id: p.id,
  title: p.title,
  comment_count: count(c.id),
  relevance: fragment("ts_rank(?, plainto_tsquery(?))", p.search_vector, ^search_term)
})
|> order_by(desc: fragment("ts_rank(?, plainto_tsquery(?))", p.search_vector, ^search_term))
|> Repo.all()

Dynamic queries

Build queries conditionally at runtime:

def list_posts(filters) do
  Post
  |> maybe_filter_by_author(filters[:author_id])
  |> maybe_filter_by_category(filters[:category_id])
  |> maybe_filter_published(filters[:published])
  |> order_by(desc: :inserted_at)
  |> Repo.all()
end

defp maybe_filter_by_author(query, nil), do: query
defp maybe_filter_by_author(query, author_id) do
  query |> where([p], p.author_id == ^author_id)
end

defp maybe_filter_by_category(query, nil), do: query
defp maybe_filter_by_category(query, category_id) do
  query |> where([p], p.category_id == ^category_id)
end

defp maybe_filter_published(query, nil), do: query
defp maybe_filter_published(query, published) do
  query |> where([p], p.published == ^published)
end

Debugging queries

See the generated SQL

query = Post
|> join(:inner, [p], c in Comment, on: c.post_id == p.id)
|> where([p], p.published == true)
|> select([p, c], {p.title, c.body})

Repo.to_sql(:all, query)
# => {"SELECT p0.\"title\", c1.\"body\" FROM \"posts\" AS p0 INNER JOIN \"comments\" AS c1 ON c1.\"post_id\" = p0.\"id\" WHERE p0.\"published\" = TRUE", []}

Enable query logging

In development, Ecto logs all queries by default. In IEx, they appear inline. To log in tests:

# In config/test.exs
config :my_app, MyApp.Repo,
  log: :debug

Quick reference

Operation	Pipe style
Inner join	`\|> join(:inner, [p], c in Comment, on: c.post_id == p.id)`
Left join	`\|> join(:left, [p], c in Comment, on: c.post_id == p.id)`
Assoc join	`\|> join(:inner, [p], c in assoc(p, :comments))`
Where	`\|> where([p], p.published == true)`
Group by	`\|> group_by([p], p.author_id)`
Having	`\|> having([p], count(p.id) > 5)`
Order by	`\|> order_by([p], desc: :inserted_at)`
Limit	`\|> limit(10)`
Select	`\|> select([p], %{title: p.title, count: count(p.id)})`
Preload	`\|> preload([p, c], comments: c)`
Fragment	`\|> where([p], fragment("lower(?)", p.title) == ^term)`