Use when querying data with Ecto.Query DSL including where clauses, joins, aggregates, preloading, and query composition. Use for building flexible database queries in Elixir applications.
Limited to specific tools
Additional assets for this skill
This skill is limited to using the following tools:
name: ecto-query-patterns description: Use when querying data with Ecto.Query DSL including where clauses, joins, aggregates, preloading, and query composition. Use for building flexible database queries in Elixir applications. allowed-tools:
Master Ecto's powerful Query DSL to build efficient, composable database queries. This skill covers the query syntax, filtering, joining, aggregation, preloading associations, and advanced query composition patterns.
import Ecto.Query, only: [from: 2]
# Basic query using keyword syntax
query = from u in "users",
where: u.age > 18,
select: u.name
# Execute the query
MyApp.Repo.all(query)
Queries are built using the from/2 macro and only sent to the database when
passed to a Repo function like all/1, one/1, or get/2. The keyword syntax
provides a readable way to construct queries.
query = from u in MyApp.User,
where: u.age > 18,
select: u.name
MyApp.Repo.all(query)
Using a schema module instead of a table name string provides better type safety and allows Ecto to use the schema's field definitions for validation and casting.
from MyApp.Post,
where: [category: "fresh and new"],
order_by: [desc: :published_at],
select: [:id, :title, :body]
Bindingless syntax allows building queries without explicit variable bindings. This works well for simple queries and when using keyword list syntax for conditions.
query = from p in MyApp.Post,
where: p.category == "fresh and new",
order_by: [desc: p.published_at],
select: struct(p, [:id, :title, :body])
MyApp.Repo.all(query)
Explicit bindings (like p for posts) allow for more complex conditions and
selections. The struct/2 function selects only specific fields from the schema.
category = "fresh and new"
order_by = [desc: :published_at]
select_fields = [:id, :title, :body]
query = from MyApp.Post,
where: [category: ^category],
order_by: ^order_by,
select: ^select_fields
MyApp.Repo.all(query)
The pin operator ^ allows interpolating Elixir values into queries. This is
essential for parameterized queries and prevents SQL injection.
query = from u in MyApp.User,
where: u.age > 0,
select: u.name
# Multiple where clauses are combined with AND
query = from u in MyApp.User,
where: u.age > 18,
where: u.confirmed == true,
select: u
MyApp.Repo.all(query)
Query expressions support field access, comparison operators, and literals.
Multiple where clauses are automatically combined with AND logic.
# Create a base query
query = from u in MyApp.User, where: u.age > 18
# Extend the query
query = from u in query, select: u.name
MyApp.Repo.all(query)
Queries are composable - you can build on existing queries by using them in the
in clause. This enables powerful query abstraction and reusability.
def most_recent_from(query, minimum_date) do
from p in query,
where: p.published_at > ^minimum_date,
order_by: [desc: p.published_at]
end
# Usage
MyApp.Post
|> most_recent_from(~N[2024-01-01 00:00:00])
|> MyApp.Repo.all()
Extracting query logic into functions creates reusable, testable query components. This pattern is fundamental to building maintainable query code.
from p in MyApp.Post,
where: p.category == "elixir" or p.category == "phoenix",
select: p
Use the or keyword for alternative conditions. For more complex OR logic,
consider using Ecto.Query.dynamic/2.
categories = ["elixir", "phoenix", "ecto"]
query = from p in MyApp.Post,
where: p.category in ^categories,
select: p
MyApp.Repo.all(query)
The in operator checks if a field value exists in a list of values. Use the
pin operator to interpolate the list variable.
search_term = "%elixir%"
query = from p in MyApp.Post,
where: like(p.title, ^search_term),
select: p
# Case-insensitive version
query = from p in MyApp.Post,
where: ilike(p.title, ^search_term),
select: p
Use like/2 for case-sensitive pattern matching and ilike/2 for case-insensitive
matching. Wildcards % match any characters.
# Select multiple fields
query = from p in MyApp.Post,
select: {p.id, p.title}
MyApp.Repo.all(query) # Returns [{1, "Title 1"}, {2, "Title 2"}]
# Select as map
query = from p in MyApp.Post,
select: %{id: p.id, title: p.title}
MyApp.Repo.all(query) # Returns [%{id: 1, title: "Title 1"}, ...]
# Select struct with specific fields
query = from p in MyApp.Post,
select: struct(p, [:id, :title, :body])
MyApp.Repo.all(query) # Returns Post structs with only selected fields loaded
Selecting specific fields instead of entire records improves query performance by reducing data transfer and memory usage.
# Count records
query = from p in MyApp.Post,
select: count(p.id)
MyApp.Repo.one(query) # Returns integer count
# Average
query = from p in MyApp.Post,
select: avg(p.rating)
# Sum
query = from o in MyApp.Order,
select: sum(o.total)
# Min and Max
query = from p in MyApp.Product,
select: {min(p.price), max(p.price)}
Ecto supports standard SQL aggregation functions including count/1, avg/1,
sum/1, min/1, and max/1.
query = from p in MyApp.Post,
group_by: p.category,
select: {p.category, count(p.id)}
MyApp.Repo.all(query) # Returns [{"elixir", 10}, {"phoenix", 5}]
# With having clause
query = from p in MyApp.Post,
group_by: p.category,
having: count(p.id) > 5,
select: {p.category, count(p.id)}
Use group_by to group results by field values and having to filter groups
based on aggregate values.
# Single field ascending
query = from p in MyApp.Post,
order_by: p.published_at
# Single field descending
query = from p in MyApp.Post,
order_by: [desc: p.published_at]
# Multiple fields
query = from p in MyApp.Post,
order_by: [desc: p.published_at, asc: p.title]
# With nulls positioning
query = from p in MyApp.Post,
order_by: [desc_nulls_last: p.published_at]
The order_by option controls result ordering. You can specify ascending or
descending order, multiple fields, and null positioning.
# Simple limit
query = from p in MyApp.Post,
limit: 10
# With offset for pagination
page = 2
per_page = 10
query = from p in MyApp.Post,
order_by: [desc: p.published_at],
limit: ^per_page,
offset: ^((page - 1) * per_page)
MyApp.Repo.all(query)
Use limit and offset for pagination. Always include an order_by clause
to ensure consistent pagination results.
query = from p in MyApp.Post,
join: c in MyApp.Comment,
on: c.post_id == p.id,
select: {p.title, c.body}
MyApp.Repo.all(query)
Inner joins return only records that have matching records in both tables. The
on clause specifies the join condition.
query = from p in MyApp.Post,
join: c in assoc(p, :comments),
select: {p, c}
MyApp.Repo.all(query)
The assoc/2 helper uses the association definition from your schema, making
joins more maintainable and less error-prone than manually specifying foreign keys.
query = from p in MyApp.Post,
left_join: c in assoc(p, :comments),
select: {p, c}
MyApp.Repo.all(query)
Left joins return all records from the left table (posts) even if there are no matching records in the right table (comments). Unmatched fields are nil.
# Preload in separate query
MyApp.Repo.all(from p in MyApp.Post, preload: [:comments])
# Preload multiple associations
MyApp.Repo.all(from p in MyApp.Post, preload: [:comments, :author])
# Nested preload
MyApp.Repo.all(from p in MyApp.Post, preload: [:author, comments: :likes])
Preloading fetches associated data efficiently, preventing N+1 query problems. Separate query preloading is simpler but may require more database round trips.
query = from p in MyApp.Post,
join: c in assoc(p, :comments),
where: c.published_at > p.updated_at,
preload: [comments: c]
MyApp.Repo.all(query)
When you join an association and want to filter it, you can preload the joined data using the binding variable. This creates a single, more efficient query.
query = from p in MyApp.Post,
join: c in assoc(p, :comments),
join: l in assoc(c, :likes),
where: l.inserted_at > c.updated_at,
preload: [:author, comments: {c, likes: l}]
MyApp.Repo.all(query)
You can preload multiple levels of nested associations while maintaining join filters. The nested tuple syntax preserves the join bindings.
posts = MyApp.Repo.all(MyApp.Post)
posts_with_comments = MyApp.Repo.preload(posts, :comments)
# Preload with custom query
comments_query = from c in MyApp.Comment, order_by: [desc: c.inserted_at]
posts_with_recent_comments = MyApp.Repo.preload(posts, comments: comments_query)
The preload/2 function can preload associations after fetching records. You
can also customize the preload query for fine-grained control.
# Define subquery
subquery = from p in MyApp.Post,
where: p.published == true,
select: %{category: p.category, count: count(p.id)},
group_by: p.category
# Use subquery
query = from s in subquery(subquery),
where: s.count > 10,
select: s.category
MyApp.Repo.all(query)
Subqueries allow using the result of one query as input to another, enabling complex analytical queries.
# Use SQL fragment
query = from p in MyApp.Post,
where: fragment("lower(?)", p.title) == "elixir",
select: p
# Fragment with parameters
search = "elixir"
query = from p in MyApp.Post,
where: fragment("lower(?) LIKE ?", p.title, ^"%#{search}%"),
select: p
The fragment/1 function allows embedding raw SQL in queries when Ecto's DSL
doesn't support a specific database feature. Use sparingly as it reduces portability.
query = from p in MyApp.Post,
hints: ["USE INDEX FOO"],
where: p.title == "title"
# Multiple hints
query = from p in MyApp.Post,
hints: "TABLESAMPLE SYSTEM(1)"
# Dynamic hints
sample = "SYSTEM_ROWS(1)"
query = from p in MyApp.Post,
hints: ["TABLESAMPLE", unsafe_fragment(^sample)]
Query hints provide database-specific optimization instructions like index usage or table sampling. Hints are database-specific and may not be portable.
defmodule MyApp.PostQueries do
import Ecto.Query
def filter(query \\ MyApp.Post, filters) do
query
|> filter_by_category(filters[:category])
|> filter_by_published(filters[:published])
|> filter_by_search(filters[:search])
end
defp filter_by_category(query, nil), do: query
defp filter_by_category(query, category) do
from p in query, where: p.category == ^category
end
defp filter_by_published(query, nil), do: query
defp filter_by_published(query, published) do
from p in query, where: p.published == ^published
end
defp filter_by_search(query, nil), do: query
defp filter_by_search(query, search) do
from p in query, where: ilike(p.title, ^"%#{search}%")
end
end
# Usage
filters = %{category: "elixir", published: true, search: "ecto"}
MyApp.PostQueries.filter(filters) |> MyApp.Repo.all()
Building queries dynamically allows handling optional filters and complex search criteria. Pattern matching on nil values keeps the code clean and readable.
defmodule MyApp.PostQueries do
import Ecto.Query
def search(filters) do
MyApp.Post
|> where(^build_where_clause(filters))
|> MyApp.Repo.all()
end
defp build_where_clause(filters) do
Enum.reduce(filters, dynamic(true), fn
{:category, value}, dynamic ->
dynamic([p], ^dynamic and p.category == ^value)
{:published, value}, dynamic ->
dynamic([p], ^dynamic and p.published == ^value)
{:min_rating, value}, dynamic ->
dynamic([p], ^dynamic and p.rating >= ^value)
_, dynamic ->
dynamic
end)
end
end
The dynamic/2 macro builds query fragments that can be composed at runtime.
This is more flexible than string-based query building and prevents SQL injection.
# Distinct on all selected fields
query = from p in MyApp.Post,
distinct: true,
select: p.category
# Distinct on specific fields
query = from p in MyApp.Post,
distinct: [desc: p.published_at],
select: p
The distinct option removes duplicate rows from results. You can specify which
fields to use for determining uniqueness.
posts_query = from p in MyApp.Post,
where: p.published == true,
select: %{type: "post", title: p.title}
pages_query = from p in MyApp.Page,
where: p.active == true,
select: %{type: "page", title: p.title}
# Union
query = posts_query |> union(^pages_query)
MyApp.Repo.all(query)
# Union all (includes duplicates)
query = posts_query |> union_all(^pages_query)
Union combines results from multiple queries. Use union/2 to remove duplicates
or union_all/2 to keep them.
# Pessimistic locking
query = from p in MyApp.Post,
where: p.id == ^post_id,
lock: "FOR UPDATE"
post = MyApp.Repo.one(query)
# Optimistic locking (using version field in schema)
changeset = MyApp.Post.changeset(post, params)
case MyApp.Repo.update(changeset) do
{:ok, updated_post} -> # Success
{:error, changeset} -> # Failed, possibly due to concurrent update
end
Locking prevents race conditions in concurrent operations. Pessimistic locking uses database locks, while optimistic locking uses version fields.
defp newest_records(parent_ids, assoc, n) do
%{related_key: related_key, queryable: queryable} = assoc
squery = from q in queryable,
where: field(q, ^related_key) == parent_as(:parent_ids).id,
order_by: {:desc, :created_at},
limit: ^n
query = from f in fragment("SELECT id from UNNEST(?::int[]) AS id", ^parent_ids),
as: :parent_ids,
inner_lateral_join: s in subquery(squery),
on: true,
select: s
MyApp.Repo.all(query)
end
Lateral joins allow subqueries that reference columns from the outer query, enabling complex correlated queries like "top N per group."
query = from p in MyApp.Post, as: :posts
query = from [posts: p] in query,
join: c in assoc(p, :comments), as: :comments
query = from [posts: p, comments: c] in query,
where: c.score > 10,
select: {p.title, c.body}
Named bindings make complex queries more readable by giving explicit names to each table or subquery in the query.
Use ecto-query-patterns when you need to:
^ for external values to prevent SQL injectionpreload to avoid N+1 query problems with associationsorder_by when using limit and offset for consistent paginationassoc/2 helper instead of manual foreign key joinsEcto.Query.dynamic/2 for complex conditional queries^, causing compilation errorsRepo.all/1 in loops instead of batch operationsorder_by when using paginationRepo.preload/2 in loops instead of batch preloading