Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 38 ยท Ecto Schemas and Queries

Ecto Queries: from, where, join and aggregates

Write composable Ecto queries with the from macro: where filters, order_by, limit, select, joins, group_by aggregates, dynamic filters and avoiding SQL injection.

Ecto queries are Elixir expressions that compile to SQL. They are composable: a query is a value you can pass around, add conditions to and run later. Because they are built with macros, Ecto checks types and escapes values for you, which protects against SQL injection.

This lesson covers from, filters, ordering, pagination, joins, aggregates and building queries from user input. You should know schemas and the Repo and basic SQL.

Ecto needs Elixir and a database, so Ecto code is read-only here. The TryIts model query building and filtering in JavaScript.

Two syntaxes

Ecto offers a keyword syntax and a pipe syntax. Both build the same query:

ELIXIR
import Ecto.Query

# keyword
query = from p in Post, where: p.published == true, order_by: [desc: p.inserted_at], limit: 10

# pipe
query =
  Post
  |> where([p], p.published == true)
  |> order_by([p], desc: p.inserted_at)
  |> limit(10)

Repo.all(query)

p is a binding: a name for the row in the query. Nothing hits the database until Repo.all, Repo.one, Repo.get or another Repo function runs it.

Filtering with where

ELIXIR
from p in Post, where: p.title == "Hello"
from p in Post, where: p.views > 100 and p.published
from p in Post, where: p.id in [1, 2, 3]
from p in Post, where: is_nil(p.deleted_at)
from p in Post, where: like(p.title, "%elixir%")
from p in Post, where: ilike(p.title, ^"%#{term}%")

To use an Elixir variable inside a query, pin it with ^. Pinned values are sent to the database as parameters, never pasted into the SQL text:

ELIXIR
min_views = 100
from p in Post, where: p.views > ^min_views

This is why Ecto queries are safe from SQL injection: only pinned values, never string concatenation. Multiple where clauses combine with AND; use or_where for OR.

Ordering, limits and selecting

ELIXIR
from p in Post, order_by: [desc: p.views, asc: p.title]
from p in Post, limit: 20, offset: 40        # page 3 with 20 per page
from p in Post, select: p.title              # list of titles
from p in Post, select: %{id: p.id, title: p.title}
from p in Post, select: {p.id, p.title}

Selecting only the columns you need reduces data transfer; the default selects the whole struct. Offset pagination slows down on deep pages, so for large tables prefer keyset pagination: where: p.id < ^last_id, order_by: [desc: p.id], limit: 20.

Fetching one result

ELIXIR
Repo.one(from p in Post, where: p.slug == ^slug)   # nil, one struct, or raises if >1
Repo.get_by(Post, slug: slug)
Repo.exists?(from p in Post, where: p.title == ^title)

Joins and associations

ELIXIR
from p in Post,
  join: a in assoc(p, :author),
  where: a.active,
  select: {p.title, a.name}

# join + preload in one query
from p in Post, join: a in assoc(p, :author), preload: [author: a]

Joins return rows combined from both tables. left_join keeps posts without a match (the joined binding may be nil). Associations are covered in associations and contexts.

Aggregates and grouping

ELIXIR
Repo.aggregate(Post, :count)
Repo.aggregate(Post, :avg, :views)

from p in Post,
  group_by: p.author_id,
  having: count(p.id) > 3,
  select: %{author_id: p.author_id, posts: count(p.id), total_views: sum(p.views)}

Composing queries

Because queries are values, small functions can each add one condition. This is the idiom for filtering by optional parameters:

ELIXIR
def list_posts(filters \\ %{}) do
  Post
  |> filter_published(filters)
  |> filter_search(filters)
  |> Repo.all()
end

defp filter_published(query, %{"published" => "true"}), do: where(query, [p], p.published)
defp filter_published(query, _), do: query

defp filter_search(query, %{"q" => q}) when q != "" do
  where(query, [p], ilike(p.title, ^"%#{q}%"))
end
defp filter_search(query, _), do: query

Beware: if q contains % or _, ilike treats them as wildcards. Escape them for strict matching.

For conditions assembled at runtime, use dynamic/2:

ELIXIR
conditions = dynamic([p], p.published)
conditions = if min, do: dynamic([p], ^conditions and p.views >= ^min), else: conditions
from p in Post, where: ^conditions

Updating and deleting in bulk

ELIXIR
from(p in Post, where: p.published == false) |> Repo.update_all(set: [published: true])
from(p in Post, where: p.inserted_at < ^cutoff) |> Repo.delete_all()
Repo.update_all(Post, inc: [views: 1])

Bulk functions return {count, nil} and skip changesets, validations and updated_at. Use them for maintenance, not for user-facing edits.

Debugging

Ecto logs each query with its time in dev. To see the SQL of a query value: Ecto.Adapters.SQL.to_sql(:all, Repo, query) returns {sql, params}. Use Repo.explain(:all, query) to inspect the plan on PostgreSQL.

A query builder, modeled

JS Composable queries over in-memory rows

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

No output.

The query is a plain value until run is called, just as an Ecto.Query waits for Repo.all.

Parameters instead of string concatenation

JS Why pinned values are safe

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

No output.

The concatenated version changes the meaning of the SQL; the parameterized version sends the text as data only. Ecto always produces the second form for ^ values.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the ^ in where: p.views > ^min mean?

    The pin operator interpolates a variable safely as a parameter, preventing SQL injection.
  2. 2. When does the database run a query built with from?

    Queries are data until you hand them to the Repo.
  3. 3. Which is best for deep pagination on a huge table?

    Keyset pagination uses an indexed condition, so cost does not grow with the page number.
  4. 4. What does Repo.update_all skip?

    update_all runs one SQL statement directly, bypassing changeset logic.

Summary

  • Queries are composable values built with from or pipe functions; the Repo runs them.
  • Pin variables with ^; Ecto sends them as parameters, which prevents SQL injection.
  • Use where, order_by, limit, select, join, group_by and aggregates; build optional filters with small functions or dynamic/2.
  • Bulk update_all and delete_all bypass changesets, so use them with care.

Next: Changesets and validations