View Source Ecto.Query (Ecto v3.12.2)
Provides the Query DSL.
Queries are used to retrieve and manipulate data from a repository
(see Ecto.Repo
). Ecto queries come in two flavors: keyword-based
and macro-based. Most examples will use the keyword-based syntax,
the macro one will be explored in later sections.
Let's see a sample query:
# Imports only from/2 of Ecto.Query
import Ecto.Query, only: [from: 2]
# Create a query
query = from u in "users",
where: u.age > 18,
select: u.name
# Send the query to the repository
Repo.all(query)
In the example above, we are directly querying the "users" table
from the database. Queries do not reach out to the data store until
they are passed as arguments to a function from Ecto.Repo
.
Query expressions
Ecto allows a limited set of expressions inside queries. In the
query below, for example, we use u.age
to access a field, the
>
comparison operator and the literal 0
:
query = from u in "users", where: u.age > 0, select: u.name
You can find the full list of operations in Ecto.Query.API
.
Besides the operations listed there, the following literals are
supported in queries:
- Integers:
1
,2
,3
- Floats:
1.0
,2.0
,3.0
- Booleans:
true
,false
- Binaries:
<<1, 2, 3>>
- Strings:
"foo bar"
,~s(this is a string)
- Atoms (other than booleans and
nil
)::foo
,:bar
- Arrays:
[1, 2, 3]
,~w(interpolate words)
All other types and dynamic values must be passed as a parameter using interpolation as explained below.
Interpolation and casting
External values and Elixir expressions can be injected into a query
expression with ^
:
def with_minimum(age, height_ft) do
from u in "users",
where: u.age > ^age and u.height > ^(height_ft * 3.28),
select: u.name
end
with_minimum(18, 5.0)
When interpolating values, you may want to explicitly tell Ecto what is the expected type of the value being interpolated:
age = "18"
Repo.all(from u in "users",
where: u.age > type(^age, :integer),
select: u.name)
In the example above, Ecto will cast the age to type integer. When
a value cannot be cast, Ecto.Query.CastError
is raised.
To avoid the repetition of always specifying the types, you may define
an Ecto.Schema
. In such cases, Ecto will analyze your queries and
automatically cast the interpolated "age" when compared to the u.age
field, as long as the age field is defined with type :integer
in
your schema:
age = "18"
Repo.all(from u in User, where: u.age > ^age, select: u.name)
Another advantage of using schemas is that we no longer need to specify the select option in queries, as by default Ecto will retrieve all fields specified in the schema:
age = "18"
Repo.all(from u in User, where: u.age > ^age)
For this reason, we will use schemas on the remaining examples but remember Ecto does not require them in order to write queries.
nil
comparison
nil
comparison in filters, such as where and having, is forbidden
and it will raise an error:
# Raises if age is nil
from u in User, where: u.age == ^age
This is done as a security measure to avoid attacks that attempt
to traverse entries with nil columns. To check that value is nil
,
use is_nil/1
instead:
from u in User, where: is_nil(u.age)
Composition
Ecto queries are composable. For example, the query above can actually be defined in two parts:
# Create a query
query = from u in User, where: u.age > 18
# Extend the query
query = from u in query, select: u.name
Composing queries uses the same syntax as creating a query.
The difference is that, instead of passing a schema like User
on the right-hand side of in
, we passed the query itself.
Any value can be used on the right-hand side of in
as long as it implements
the Ecto.Queryable
protocol. For now, we know the protocol is
implemented for both atoms (like User
) and strings (like "users").
In any case, regardless if a schema has been given or not, Ecto queries are always composable thanks to its binding system.
Positional bindings
On the left-hand side of in
we specify the query bindings. This is
done inside from
and join
clauses. In the query below u
is a
binding and u.age
is a field access using this binding.
query = from u in User, where: u.age > 18
Bindings are not exposed from the query. When composing queries, you must specify bindings again for each refinement query. For example, to further narrow down the above query, we again need to tell Ecto what bindings to expect:
query = from u in query, select: u.city
Bindings in Ecto are positional, and the names do not have to be consistent between input and refinement queries. For example, the query above could also be written as:
query = from q in query, select: q.city
It would make no difference to Ecto. This is important because it allows developers to compose queries without caring about the bindings used in the initial query.
When using joins, the bindings should be matched in the order they are specified:
# Create a query
query = from p in Post,
join: c in Comment, on: c.post_id == p.id
# Extend the query
query = from [p, c] in query,
select: {p.title, c.body}
You are not required to specify all bindings when composing. For example, if we would like to order the results above by post insertion date, we could further extend it as:
query = from q in query, order_by: q.inserted_at
The example above will work if the input query has 1 or 10
bindings. As long as the number of bindings is less than the
number of from
s + join
s, Ecto will match only what you have
specified. The first binding always matches the source given
in from
.
Similarly, if you are interested only in the last binding
(or the last bindings) in a query, you can use ...
to
specify "all bindings before" and match on the last one.
For instance, imagine you wrote:
posts_with_comments =
from p in query, join: c in Comment, on: c.post_id == p.id
And now we want to make sure to return both the post title and the comment body. Although we may not know how many bindings there are in the query, we are sure posts is the first binding and comments are the last one, so we can write:
from [p, ..., c] in posts_with_comments, select: {p.title, c.body}
In other words, ...
will include all the bindings between the
first and the last, which may be one, many or no bindings at all.
Named bindings
Another option for flexibly building queries with joins are named
bindings. Coming back to the previous example, we can use the
as: :comment
option to bind the comments join to a concrete name:
posts_with_comments =
from p in Post,
join: c in Comment, as: :comment, on: c.post_id == p.id
Now we can refer to it using the following form of a bindings list:
from [p, comment: c] in posts_with_comments, select: {p.title, c.body}
This approach lets us not worry about keeping track of the position
of the bindings when composing the query. The :as
option can be
given both on joins and on from
:
from p in Post, as: :post
Only atoms are accepted for binding names. Named binding references must always be placed at the end of the bindings list:
[positional_binding_1, positional_binding_2, named_1: binding, named_2: binding]
Named bindings can also be used for late binding with the as/1
construct, allowing you to refer to a binding that has not been
defined yet:
from c in Comment, where: as(:posts).id == c.post_id
This is especially useful when working with subqueries, where you
may need to refer to a parent binding with parent_as
, which is
not known when writing the subquery:
child_query = from c in Comment, where: parent_as(:posts).id == c.post_id
from p in Post, as: :posts, inner_lateral_join: c in subquery(child_query)
You can also match on a specific binding when building queries. For
example, let's suppose you want to create a generic sort function
that will order by a given field
with a given as
in query
:
# Knowing the name of the binding
def sort(query, as, field) do
from [{^as, x}] in query, order_by: field(x, ^field)
end
Bindingless operations
Although bindings are extremely useful when working with joins,
they are not necessary when the query has only the from
clause.
For such cases, Ecto supports a way for building queries
without specifying the binding:
from Post,
where: [category: "fresh and new"],
order_by: [desc: :published_at],
select: [:id, :title, :body]
The query above will select all posts with category "fresh and new", order by the most recently published, and return Post structs with only the id, title and body fields set. It is equivalent to:
from p in Post,
where: p.category == "fresh and new",
order_by: [desc: p.published_at],
select: struct(p, [:id, :title, :body])
One advantage of bindingless queries is that they are data-driven and therefore useful for dynamically building queries. For example, the query above could also be written as:
where = [category: "fresh and new"]
order_by = [desc: :published_at]
select = [:id, :title, :body]
from Post, where: ^where, order_by: ^order_by, select: ^select
This feature is very useful when queries need to be built based on some user input, like web search forms, CLIs and so on.
Fragments
If you need an escape hatch, Ecto provides fragments
(see Ecto.Query.API.fragment/1
) to inject SQL (and non-SQL)
fragments into queries.
For example, to get all posts while running the "lower(?)"
function in the database where p.title
is interpolated
in place of ?
, one can write:
from p in Post,
where: is_nil(p.published_at) and
fragment("lower(?)", p.title) == ^title
Also, most adapters provide direct APIs for queries, like
Ecto.Adapters.SQL.query/4
, allowing developers to
completely bypass Ecto queries.
Macro API
In all examples so far we have used the keywords query syntax to create a query:
import Ecto.Query
from u in "users", where: u.age > 18, select: u.name
Due to the prevalence of the pipe operator in Elixir, Ecto also supports a pipe-based syntax:
"users"
|> where([u], u.age > 18)
|> select([u], u.name)
The keyword-based and pipe-based examples are equivalent. The downside
of using macros is that the binding must be specified for every operation.
However, since keyword-based and pipe-based examples are equivalent, the
bindingless syntax also works for macros. Please note that the following
example is not completely equivalent to the previous example,
as it does not return the name but rather the User
struct:
"users"
|> where([u], u.age > 18)
|> select([:name])
Such a syntax allows developers to write queries using bindings only in more complex query expressions.
This module documents each of those macros, providing examples in both the keywords query and pipe expression formats.
Query prefix
It is possible to set a prefix for the queries. For Postgres users, this will specify the schema where the table is located, while for MySQL users this will specify the database where the table is located. When no prefix is set, Postgres queries are assumed to be in the public schema, while MySQL queries are assumed to be in the database set in the config for the repo.
The query prefix may be set either for the whole query or on each
individual from
and join
expression. If a prefix
is not given
to a from
or a join
, the prefix of the schema given to the from
or join
is used. The query prefix is used only if none of the above
are declared.
Let's see some examples. To set the query prefix globally, the simplest mechanism is to pass an option to the repository operation:
results = Repo.all(query, prefix: "accounts")
You may also set the prefix for the whole query by setting the prefix field:
results =
query # May be User or an Ecto.Query itself
|> Ecto.Query.put_query_prefix("accounts")
|> Repo.all()
Setting the prefix in the query changes the default prefix of all from
and join
expressions. You can override the query prefix by either setting
the @schema_prefix
in your schema definitions or by passing the prefix
option:
from u in User,
prefix: "accounts",
join: p in assoc(u, :posts),
prefix: "public"
Overall, here is the prefix lookup precedence:
- The
:prefix
option given tofrom
/join
has the highest precedence - Then it falls back to the
@schema_prefix
attribute declared in the schema given tofrom
/join
- Then it falls back to the query prefix. The query prefix may be
set either on the query with
put_query_prefix/2
or by passing the:prefix
option when calling theRepo
module (where the former wins if both methods are used)
The prefixes set in the query will be preserved when loading data.
Summary
Functions
The Ecto.Query
struct.
A distinct query expression.
Builds a dynamic query expression.
An except (set difference) query expression.
An except (set difference) query expression.
Resets a previously set field on a query.
Restricts the query to return the first result ordered by primary key.
Creates a query.
A group by query expression.
Returns true
if the query has a binding with the given name, otherwise false
.
An AND having query expression.
An intersect query expression.
An intersect query expression.
The same as has_named_binding?/2
but allowed in guards.
A join query expression.
Restricts the query to return the last result ordered by primary key.
A limit query expression.
A lock query expression.
An offset query expression.
An OR having query expression.
An OR where query expression.
An order by query expression.
Preloads the associations into the result set.
An order by query expression that is prepended to existing ones.
Puts the given prefix in a query.
Enables or disables recursive mode for CTEs.
Reverses the ordering of the query.
A select query expression.
Mergeable select query expression.
Converts a query into a subquery.
A union query expression.
A union all query expression.
An update query expression.
An AND where query expression.
Defines windows which can be used with Ecto.Query.WindowAPI
.
A common table expression (CTE) also known as WITH expression.
Applies a callback function to a query if it doesn't contain the given named binding. Otherwise, returns the original query.
Enables or disables ties for limit expressions.
Types
@type t() :: %Ecto.Query{ aliases: term(), assocs: term(), combinations: term(), distinct: term(), from: term(), group_bys: term(), havings: term(), joins: term(), limit: term(), lock: term(), offset: term(), order_bys: term(), prefix: term(), preloads: term(), select: term(), sources: term(), updates: term(), wheres: term(), windows: term(), with_ctes: term() }
Functions
The Ecto.Query
struct.
Users of Ecto must consider this struct as opaque and not access its field directly. Authors of adapters may read its contents, but never modify them.
A distinct query expression.
When true, only keeps distinct values from the resulting select expression.
If supported by your database, you can also pass query expressions
to distinct and it will generate a query with DISTINCT ON. In such
cases, distinct
accepts exactly the same expressions as order_by
and any distinct
expression will be automatically prepended to the
order_by
expressions in case there is any order_by
expression.
Keywords examples
# Returns the list of different categories in the Post schema
from(p in Post, distinct: true, select: p.category)
# If your database supports DISTINCT ON(),
# you can pass expressions to distinct too
from(p in Post,
distinct: p.category,
order_by: [p.date])
# The DISTINCT ON() also supports ordering similar to ORDER BY.
from(p in Post,
distinct: [desc: p.category],
order_by: [p.date])
# Using atoms
from(p in Post, distinct: :category, order_by: :date)
Expressions example
Post
|> distinct(true)
|> order_by([p], [p.category, p.author])
Builds a dynamic query expression.
Dynamic query expressions allow developers to compose query expressions bit by bit, so that they can be interpolated into parts of a query or another dynamic expression later on.
Examples
Imagine you have a set of conditions you want to build your query on:
conditions = false
conditions =
if params["is_public"] do
dynamic([p], p.is_public or ^conditions)
else
conditions
end
conditions =
if params["allow_reviewers"] do
dynamic([p, a], a.reviewer == true or ^conditions)
else
conditions
end
from query, where: ^conditions
In the example above, we were able to build the query expressions bit by bit, using different bindings, and later interpolate it all at once into the actual query.
A dynamic expression can always be interpolated inside another dynamic expression and into the constructs described below.
where
, having
and a join
's on
The dynamic
macro can be interpolated at the root of a where
,
having
or a join
's on
.
For example, assuming the conditions
variable defined in the
previous section, the following is forbidden because it is not
at the root of a where
:
from q in query, where: q.some_condition and ^conditions
Fortunately that's easily solved by simply rewriting it to:
conditions = dynamic([q], q.some_condition and ^conditions)
from query, where: ^conditions
Dynamic boundaries
Type casting does not cross dynamic boundaries. When you write a dynamic expression, such as
dynamic([p], p.visits > ^param)
, Ecto will automatically cast^param
to the type ofp.visits
.However, if
p.visits
is in itself dynamic, as in the example below, then Ecto won't be able to propagate its type to^param
:field = dynamic([p], p.visits) dynamic(^field > ^param)
order_by
Dynamics can be interpolated inside keyword lists at the root of
order_by
. For example, you can write:
order_by = [
asc: :some_field,
desc: dynamic([p], fragment("?->>?", p.another_field, "json_key"))
]
from query, order_by: ^order_by
Dynamics are also supported in order_by/2
clauses inside windows/2
.
As with where
and friends, it is not possible to pass dynamics
outside of a root. For example, this won't work:
from query, order_by: [asc: ^dynamic(...)]
But this will:
from query, order_by: ^[asc: dynamic(...)]
group_by
Dynamics can be interpolated inside keyword lists at the root of
group_by
. For example, you can write:
group_by = [
:some_field,
dynamic([p], fragment("?->>?", p.another_field, "json_key"))
]
from query, group_by: ^group_by
Dynamics are also supported in partition_by/2
clauses inside windows/2
.
As with where
and friends, it is not possible to pass dynamics
outside of a root. For example, this won't work:
from query, group_by: [:some_field, ^dynamic(...)]
But this will:
from query, group_by: ^[:some_field, dynamic(...)]
select
and select_merge
Dynamics can be inside maps interpolated at the root of a
select
or select_merge
. For example, you can write:
fields = %{
period: dynamic([p], p.month),
metric: dynamic([p], p.distance)
}
from query, select: ^fields
As with where
and friends, it is not possible to pass dynamics
outside of a root. For example, this won't work:
from query, select: %{field: ^dynamic(...)}
But this will:
from query, select: ^%{field: dynamic(...)}
Maps with dynamics can also be merged into existing select
structures,
enabling a variety of possibilities for partially dynamic selects:
metric = dynamic([p], p.distance)
from query, select: [:period, :metric], select_merge: ^%{metric: metric}
Aliasing fields with selected_as/2
and referencing them with selected_as/1
is also allowed:
fields = %{
period: dynamic([p], selected_as(p.month, :month)),
metric: dynamic([p], p.distance)
}
order = dynamic(selected_as(:month))
from query, select: ^fields, order_by: ^order
update
A dynamic
is also supported inside updates, for example:
updates = [
set: [average: dynamic([p], p.sum / p.count)]
]
from query, update: ^updates
preload
Dynamics can be used with preload
in order to dynamically
specify the binding for a joined association. For example, you can
write:
preloads = [
:non_joined_assoc,
joined_assoc: dynamic([joined: j], j)
]
from x in query,
join: assoc(x, :joined_assoc),
as: :joined,
preload: ^preloads
While the example above uses a named binding (:joined
),
positional bindings may also be used:
preloads = [
:non_joined_assoc,
joined_assoc: dynamic([_, j], j)
]
from x in query,
join: assoc(x, :joined_assoc)
preload: ^preloads
As with where
and friends, it is not possible to pass dynamics
outside of an interpolated root. For example, this won't work:
from query, preload: [comments: ^dynamic(...)]
But this will:
from query, preload: ^[comments: dynamic(...)]
Dynamic expressions used in preload
must evaluate to a single
binding. For instance, this won't work:
preloads = dynamic([comments: c, likes: l], [comments: {c, likes: l}])
But this will:
dynamic_comments = dynamic([comments: c], c)
dynamic_likes = dynamic([likes: l], l)
preloads = [
comments: {dynamic_comments, likes: dynamic_likes}
]
An except (set difference) query expression.
Takes the difference of the result sets of multiple queries. The
select
of each query must be exactly the same, with the same
types in the same order.
Except expression returns only unique rows as if each query returned
distinct results. This may cause a performance penalty. If you need
to take the difference of multiple result sets without
removing duplicate rows consider using except_all/2
.
Combination behaviour
There are several behaviours of combination queries that must be taken into account, otherwise you may unexpectedly return the wrong query result.
Order by, limit and offset
The order_by
, limit
and offset
expressions of the parent query apply
to the result of the entire combination. order_by
must be specified in one
of the following ways, since the combination of two or more queries is not
automatically aliased:
- Use
Ecto.Query.API.fragment/1
to pass anorder_by
statement that directly access the combination fields. - Wrap the combination in a subquery and refer to the binding of the subquery.
Column selection ordering
The columns of each of the queries in the combination must be specified in the exact same order. Otherwise, you may see the values of one column appearing in another. This holds for all types of select expressions, including maps.
For example, the following query will interchange the values of the supplier's name and city because that is the order the fields are specified in the customer query.
supplier_query = from s in Supplier, select: %{city: s.city, name: s.name}
customer_query = from c in Customer, select: %{name: c.name, city: c.city}
except(supplier_query, ^customer_query)
Selecting literal atoms
When selecting a literal atom, its value must be the same across all queries. Otherwise, the value from the parent query will be applied to all other queries. This also holds true for selecting maps with atom keys.
Keywords examples
# Unordered result
supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, except: ^supplier_query
# Ordered result
supplier_query = from s in Supplier, select: s.city
except_query = from c in Customer, select: c.city, except: ^supplier_query
from s in subquery(except_query), order_by: s.city
Expressions examples
# Unordered result
supplier_query = Supplier |> select([s], s.city)
Customer |> select([c], c.city) |> except(^supplier_query)
# Ordered result
customer_query = Customer |> select([c], c.city) |> order_by(fragment("city"))
supplier_query = Supplier |> select([s], s.city)
except(customer_query, ^supplier_query)
An except (set difference) query expression.
Takes the difference of the result sets of multiple queries. The
select
of each query must be exactly the same, with the same
types in the same order.
Combination behaviour
There are several behaviours of combination queries that must be taken into account, otherwise you may unexpectedly return the wrong query result.
Order by, limit and offset
The order_by
, limit
and offset
expressions of the parent query apply
to the result of the entire combination. order_by
must be specified in one
of the following ways, since the combination of two or more queries is not
automatically aliased:
- Use
Ecto.Query.API.fragment/1
to pass anorder_by
statement that directly access the combination fields. - Wrap the combination in a subquery and refer to the binding of the subquery.
Column selection ordering
The columns of each of the queries in the combination must be specified in the exact same order. Otherwise, you may see the values of one column appearing in another. This holds for all types of select expressions, including maps.
For example, the following query will interchange the values of the supplier's name and city because that is the order the fields are specified in the customer query.
supplier_query = from s in Supplier, select: %{city: s.city, name: s.name}
customer_query = from c in Customer, select: %{name: c.name, city: c.city}
except_all(supplier_query, ^customer_query)
Selecting literal atoms
When selecting a literal atom, its value must be the same across all queries. Otherwise, the value from the parent query will be applied to all other queries. This also holds true for selecting maps with atom keys.
Keywords examples
# Unordered result
supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, except_all: ^supplier_query
# Ordered result
supplier_query = from s in Supplier, select: s.city
except_all_query = from c in Customer, select: c.city, except_all: ^supplier_query
from s in subquery(except_all_query), order_by: s.city
Expressions examples
# Unordered result
supplier_query = Supplier |> select([s], s.city)
Customer |> select([c], c.city) |> except_all(^supplier_query)
# Ordered result
customer_query = Customer |> select([c], c.city) |> order_by(fragment("city"))
supplier_query = Supplier |> select([s], s.city)
except_all(customer_query, ^supplier_query)
Resets a previously set field on a query.
It can reset many fields except the query source (from
). When excluding
a :join
, it will remove all types of joins. If you prefer to remove a
single type of join, please see paragraph below.
Examples
Ecto.Query.exclude(query, :join)
Ecto.Query.exclude(query, :where)
Ecto.Query.exclude(query, :order_by)
Ecto.Query.exclude(query, :group_by)
Ecto.Query.exclude(query, :having)
Ecto.Query.exclude(query, :distinct)
Ecto.Query.exclude(query, :select)
Ecto.Query.exclude(query, :combinations)
Ecto.Query.exclude(query, :with_ctes)
Ecto.Query.exclude(query, :limit)
Ecto.Query.exclude(query, :offset)
Ecto.Query.exclude(query, :lock)
Ecto.Query.exclude(query, :preload)
Ecto.Query.exclude(query, :update)
You can also remove specific joins as well such as left_join
and
inner_join
:
Ecto.Query.exclude(query, :inner_join)
Ecto.Query.exclude(query, :cross_join)
Ecto.Query.exclude(query, :cross_lateral_join)
Ecto.Query.exclude(query, :left_join)
Ecto.Query.exclude(query, :right_join)
Ecto.Query.exclude(query, :full_join)
Ecto.Query.exclude(query, :inner_lateral_join)
Ecto.Query.exclude(query, :left_lateral_join)
However, keep in mind that if a join is removed and its bindings were referenced elsewhere, the bindings won't be removed, leading to a query that won't compile.
Restricts the query to return the first result ordered by primary key.
The query will be automatically ordered by the primary key
unless order_by
is given or order_by
is set in the query.
Limit is always set to 1.
Examples
Post |> first |> Repo.one
query |> first(:inserted_at) |> Repo.one
Creates a query.
It can either be a keyword query or a query expression.
If it is a keyword query the first argument must be
either an in
expression, a value that implements
the Ecto.Queryable
protocol, or an Ecto.Query.API.fragment/1
. If the query needs a
reference to the data source in any other part of the
expression, then an in
must be used to create a reference
variable. The second argument should be a keyword query
where the keys are expression types and the values are
expressions.
If it is a query expression the first argument must be
a value that implements the Ecto.Queryable
protocol
and the second argument the expression.
Hints
The hints
keyword can be used to specify query hints:
from p in Post,
hints: ["USE INDEX FOO"],
where: p.title == "title"
It can also be used as a general mechanism for adding statements that
come after the from
clause. For example, it can be used to enable
table sampling:
from p in Post,
hints: "TABLESAMPLE SYSTEM(1)"
from
hints must be a (list of) compile-time strings or unsafe fragments. An unsafe
fragment can be used to specify dynamic hints:
sample = "SYSTEM_ROWS(1)"
from p in Post,
hints: ["TABLESAMPLE", unsafe_fragment(^sample)]
Unsafe Fragments
The output of
unsafe_fragment/1
will be injected directly into the resulting SQL statement without being escaped. For this reason, input from uncontrolled sources, such as user input, should never be used. Otherwise, it could lead to harmful SQL injection attacks.
Keywords examples
# `in` expression
from(c in City, select: c)
# Ecto.Queryable
from(City, limit: 1)
# Fragment with user-defined function and predefined columns
from(f in fragment("my_table_valued_function(arg)"), select: f.x)
# Fragment with built-in function and undefined columns
from(f in fragment("select generate_series(?::integer, ?::integer) as x", ^0, ^10), select: f.x)
Expressions examples
# Schema
City |> select([c], c)
# Source
"cities" |> select([c], c)
# Source with schema
{"cities", Source} |> select([c], c)
# Ecto.Query
from(c in Cities) |> select([c], c)
Examples
def paginate(query, page, size) do
from query,
limit: ^size,
offset: ^((page-1) * size)
end
The example above does not use in
because limit
and offset
do not require a reference to the data source. However, extending
the query with a where expression would require the use of in
:
def published(query) do
from p in query, where: not(is_nil(p.published_at))
end
Notice we have created a p
variable to reference the query's
original data source. This assumes that the original query
only had one source. When the given query has more than one source,
positional or named bindings may be used to access the additional sources.
def published_multi(query) do
from [p,o] in query,
where: not(is_nil(p.published_at)) and not(is_nil(o.published_at))
end
Note that the variables p
and o
can be named whatever you like
as they have no importance in the query sent to the database.
A group by query expression.
Groups together rows from the schema that have the same values in the given
fields. Using group_by
"groups" the query giving it different semantics
in the select
expression. If a query is grouped, only fields that were
referenced in the group_by
can be used in the select
or if the field
is given as an argument to an aggregate function.
group_by
also accepts a list of atoms where each atom refers to
a field in source. For more complicated queries you can access fields
directly instead of atoms.
Keywords examples
# Returns the number of posts in each category
from(p in Post,
group_by: p.category,
select: {p.category, count(p.id)})
# Using atoms
from(p in Post, group_by: :category, select: {p.category, count(p.id)})
# Using direct fields access
from(p in Post,
join: c in assoc(p, :category),
group_by: [p.id, c.name])
Expressions example
Post |> group_by([p], p.category) |> select([p], count(p.id))
Returns true
if the query has a binding with the given name, otherwise false
.
For more information on named bindings see "Named bindings" in this module doc.
An AND having query expression.
Like where
, having
filters rows from the schema, but after the grouping is
performed giving it the same semantics as select
for a grouped query
(see group_by/3
). having
groups the query even if the query has no
group_by
expression.
Keywords example
# Returns the number of posts in each category where the
# average number of comments is above ten
from(p in Post,
group_by: p.category,
having: avg(p.num_comments) > 10,
select: {p.category, count(p.id)})
Expressions example
Post
|> group_by([p], p.category)
|> having([p], avg(p.num_comments) > 10)
|> select([p], count(p.id))
An intersect query expression.
Takes the overlap of the result sets of multiple queries. The
select
of each query must be exactly the same, with the same
types in the same order.
Intersect expression returns only unique rows as if each query returned
distinct results. This may cause a performance penalty. If you need
to take the intersection of multiple result sets without
removing duplicate rows consider using intersect_all/2
.
Combination behaviour
There are several behaviours of combination queries that must be taken into account, otherwise you may unexpectedly return the wrong query result.
Order by, limit and offset
The order_by
, limit
and offset
expressions of the parent query apply
to the result of the entire combination. order_by
must be specified in one
of the following ways, since the combination of two or more queries is not
automatically aliased:
- Use
Ecto.Query.API.fragment/1
to pass anorder_by
statement that directly access the combination fields. - Wrap the combination in a subquery and refer to the binding of the subquery.
Column selection ordering
The columns of each of the queries in the combination must be specified in the exact same order. Otherwise, you may see the values of one column appearing in another. This holds for all types of select expressions, including maps.
For example, the following query will interchange the values of the supplier's name and city because that is the order the fields are specified in the customer query.
supplier_query = from s in Supplier, select: %{city: s.city, name: s.name}
customer_query = from c in Customer, select: %{name: c.name, city: c.city}
intersect(supplier_query, ^customer_query)
Selecting literal atoms
When selecting a literal atom, its value must be the same across all queries. Otherwise, the value from the parent query will be applied to all other queries. This also holds true for selecting maps with atom keys.
Keywords examples
# Unordered result
supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, intersect: ^supplier_query
# Ordered result
supplier_query = from s in Supplier, select: s.city
intersect_query = from c in Customer, select: c.city, intersect: ^supplier_query
from s in subquery(intersect_query), order_by: s.city
Expressions examples
# Unordered result
supplier_query = Supplier |> select([s], s.city)
Customer |> select([c], c.city) |> intersect(^supplier_query)
# Ordered result
customer_query = Customer |> select([c], c.city) |> order_by(fragment("city"))
supplier_query = Supplier |> select([s], s.city)
intersect(customer_query, ^supplier_query)
An intersect query expression.
Takes the overlap of the result sets of multiple queries. The
select
of each query must be exactly the same, with the same
types in the same order.
Combination behaviour
There are several behaviours of combination queries that must be taken into account, otherwise you may unexpectedly return the wrong query result.
Order by, limit and offset
The order_by
, limit
and offset
expressions of the parent query apply
to the result of the entire combination. order_by
must be specified in one
of the following ways, since the combination of two or more queries is not
automatically aliased:
- Use
Ecto.Query.API.fragment/1
to pass anorder_by
statement that directly access the combination fields. - Wrap the combination in a subquery and refer to the binding of the subquery.
Column selection ordering
The columns of each of the queries in the combination must be specified in the exact same order. Otherwise, you may see the values of one column appearing in another. This holds for all types of select expressions, including maps.
For example, the following query will interchange the values of the supplier's name and city because that is the order the fields are specified in the customer query.
supplier_query = from s in Supplier, select: %{city: s.city, name: s.name}
customer_query = from c in Customer, select: %{name: c.name, city: c.city}
intersect_all(supplier_query, ^customer_query)
Selecting literal atoms
When selecting a literal atom, its value must be the same across all queries. Otherwise, the value from the parent query will be applied to all other queries. This also holds true for selecting maps with atom keys.
Keywords examples
# Unordered result
supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, intersect_all: ^supplier_query
# Ordered result
supplier_query = from s in Supplier, select: s.city
intersect_all_query = from c in Customer, select: c.city, intersect_all: ^supplier_query
from s in subquery(intersect_all_query), order_by: s.city
Expressions examples
# Unordered result
supplier_query = Supplier |> select([s], s.city)
Customer |> select([c], c.city) |> intersect_all(^supplier_query)
# Ordered result
customer_query = Customer |> select([c], c.city) |> order_by(fragment("city"))
supplier_query = Supplier |> select([s], s.city)
intersect_all(customer_query, ^supplier_query)
The same as has_named_binding?/2
but allowed in guards.
A join query expression.
Receives a source that is to be joined to the query and a condition for
the join. The join condition can be any expression that evaluates
to a boolean value. The qualifier must be one of :inner
, :left
,
:right
, :cross
, :cross_lateral
, :full
, :inner_lateral
or :left_lateral
.
For a keyword query the :join
keyword can be changed to :inner_join
,
:left_join
, :right_join
, :cross_join
, :cross_lateral_join
, :full_join
, :inner_lateral_join
or :left_lateral_join
. :join
is equivalent to :inner_join
.
Currently it is possible to join on:
- an
Ecto.Schema
, such asp in Post
- an interpolated Ecto query with zero or more
where
clauses, such asc in ^(from "posts", where: [public: true])
- an association, such as
c in assoc(post, :comments)
- a subquery, such as
c in subquery(another_query)
- a query fragment, such as
c in fragment("SOME COMPLEX QUERY")
, see "Joining with fragments" below.
Options
Each join accepts the following options:
:on
- a query expression or keyword list to filter the join, defaults totrue
:as
- a named binding for the join:prefix
- the prefix to be used for the join when issuing a database query:hints
- a string or a list of strings to be used as database hints
In the keyword query syntax, those options must be given immediately after the join. In the expression syntax, the options are given as the fifth argument.
Unspecified join condition
Leaving the
:on
option unspecified while performing a join that is not a cross join will trigger a warning. This is to help users avoid performing expensive cross joins when they don't mean to. If the behaviour is desired, you may remove the warning by changing to a cross join or explicitly settingon: true
. If the behaviour is not desired, you should specify the appropriate join condition.
Keywords examples
from c in Comment,
join: p in Post,
on: p.id == c.post_id,
select: {p.title, c.text}
from p in Post,
left_join: c in assoc(p, :comments),
select: {p, c}
Keywords can also be given or interpolated as part of on
:
from c in Comment,
join: p in Post,
on: [id: c.post_id],
select: {p.title, c.text}
Any key in on
will apply to the currently joined expression.
It is also possible to interpolate an Ecto query on the right-hand side
of in
. For example, the query above can also be written as:
posts = Post
from c in Comment,
join: p in ^posts,
on: [id: c.post_id],
select: {p.title, c.text}
The above is specially useful to dynamically join on existing queries, for example, to dynamically choose a source, or by choosing between public posts or posts that have been recently published:
posts =
if params["drafts"] do
from p in Post, where: [drafts: true]
else
from p in Post, where: [public: true]
end
from c in Comment,
join: p in ^posts, on: [id: c.post_id],
select: {p.title, c.text}
Only simple queries with where
expressions can be interpolated
in a join.
Expressions examples
Comment
|> join(:inner, [c], p in Post, on: c.post_id == p.id)
|> select([c, p], {p.title, c.text})
Post
|> join(:left, [p], c in assoc(p, :comments))
|> select([p, c], {p, c})
Post
|> join(:left, [p], c in Comment, on: c.post_id == p.id and c.is_visible == true)
|> select([p, c], {p, c})
Joining with fragments
When you need to join on a complex query, Ecto supports fragments in joins:
Comment
|> join(:inner, [c], p in fragment("SOME COMPLEX QUERY", c.id, ^some_param))
Note that the join
does not automatically wrap the fragment in
parentheses, since some expressions require parens and others
require no parens. Therefore, in cases such as common table
expressions, you will have to explicitly wrap the fragment content
in parens.
Lateral Joins
Lateral joins require a subquery that refer to previous bindings. This can be achieved using
parent_as
within the subquery
function:
Game
|> from(as: :game)
|> join(
:inner_lateral,
[],
subquery(
GamesSold
|> where([gs], gs.game_id == parent_as(:game).id)
|> order_by([gs], gs.sold_on)
|> limit(2)
),
on: true
)
|> select([g, gs], {g.name, gs.sold_on})
Hints
join
also supports table hints, as found in databases such as
MySQL,
MSSQL and
Clickhouse.
For example, a developer using MySQL may write:
from p in Post,
join: c in Comment,
hints: ["USE INDEX FOO", "USE INDEX BAR"],
where: p.id == c.post_id,
select: c
Keep in mind you want to use hints rarely, so don't forget to read the database disclaimers about such functionality.
Join hints must be static compile-time strings when they are specified as (list of) strings.
Restricts the query to return the last result ordered by primary key.
The query ordering will be automatically reversed, with ASC columns becoming DESC columns (and vice-versa) and limit is set to 1. If there is no ordering, the query will be automatically ordered decreasingly by primary key.
Examples
Post |> last |> Repo.one
query |> last(:inserted_at) |> Repo.one
A limit query expression.
Limits the number of rows returned from the result. Can be any expression but has to evaluate to an integer value and it can't include any field.
If limit
is given twice, it overrides the previous value.
Keywords example
from(u in User, where: u.id == ^current_user, limit: 1)
Expressions example
User |> where([u], u.id == ^current_user) |> limit(1)
A lock query expression.
Provides support for row-level pessimistic locking using
SELECT ... FOR UPDATE
or other, database-specific, locking clauses.
expr
can be any expression but has to evaluate to a boolean value or to a
string and it can't include any fields.
If lock
is used more than once, the last one used takes precedence.
Ecto also supports optimistic
locking but not
through queries. For more information on optimistic locking, have a look at
the Ecto.Changeset.optimistic_lock/3
function.
Keywords example
from(u in User, where: u.id == ^current_user, lock: "FOR SHARE NOWAIT")
Expressions example
User |> where([u], u.id == ^current_user) |> lock("FOR SHARE NOWAIT")
An offset query expression.
Offsets the number of rows selected from the result. Can be any expression but it must evaluate to an integer value and it can't include any field.
If offset
is given twice, it overrides the previous value.
Keywords example
# Get all posts on page 4
from(p in Post, limit: 10, offset: 30)
Expressions example
Post |> limit(10) |> offset(30)
An OR having query expression.
Like having
but combines with the previous expression by using
OR
. or_having
behaves for having
the same way or_where
behaves for where
.
Keywords example
# Augment a previous group_by with a having condition.
from(p in query, or_having: avg(p.num_comments) > 10)
Expressions example
# Augment a previous group_by with a having condition.
Post |> or_having([p], avg(p.num_comments) > 10)
An OR where query expression.
Behaves exactly the same as where
except it combines with any previous
expression by using an OR
. All expressions have to evaluate to a boolean
value.
or_where
also accepts a keyword list where each key is a field to be
compared with the given value. Each key-value pair will be combined
using AND
, exactly as in where
.
Keywords example
from(c in City, where: [country: "Sweden"], or_where: [country: "Brazil"])
If interpolating keyword lists, the keyword list entries are combined using ANDs and joined to any existing expression with an OR:
filters = [country: "USA", name: "New York"]
from(c in City, where: [country: "Sweden"], or_where: ^filters)
is equivalent to:
from c in City, where: (c.country == "Sweden") or
(c.country == "USA" and c.name == "New York")
The behaviour above is by design to keep the changes between where
and or_where
minimal. Plus, if you have a keyword list and you
would like each pair to be combined using or
, it can be easily done
with Enum.reduce/3
:
filters = [country: "USA", is_tax_exempt: true]
Enum.reduce(filters, City, fn {key, value}, query ->
from q in query, or_where: field(q, ^key) == ^value
end)
which will be equivalent to:
from c in City, or_where: (c.country == "USA"), or_where: c.is_tax_exempt == true
Expressions example
City |> where([c], c.country == "Sweden") |> or_where([c], c.country == "Brazil")
An order by query expression.
Orders the fields based on one or more fields. It accepts a single field
or a list of fields. The default direction is ascending (:asc
) and can be
customized in a keyword list as one of the following:
:asc
:asc_nulls_last
:asc_nulls_first
:desc
:desc_nulls_last
:desc_nulls_first
The *_nulls_first
and *_nulls_last
variants are not supported by all
databases. While all databases default to ascending order, the choice of
"nulls first" or "nulls last" is specific to each database implementation.
order_by
may be invoked or listed in a query many times. New expressions
are appended to the existing ones.
order_by
also accepts a list of atoms where each atom refers to a field in
source or a keyword list where the direction is given as key and the field
to order as value.
Keywords examples
from(c in City, order_by: c.name, order_by: c.population)
from(c in City, order_by: [c.name, c.population])
from(c in City, order_by: [asc: c.name, desc: c.population])
from(c in City, order_by: [:name, :population])
from(c in City, order_by: [asc: :name, desc_nulls_first: :population])
A keyword list can also be interpolated:
values = [asc: :name, desc_nulls_first: :population]
from(c in City, order_by: ^values)
A fragment can also be used:
from c in City, order_by: [
# A deterministic shuffled order
fragment("? % ? DESC", c.id, ^modulus),
desc: c.id,
]
It's also possible to order by an aliased or calculated column:
from(c in City,
select: %{
name: c.name,
total_population:
fragment(
"COALESCE(?, ?) + ? AS total_population",
c.animal_population,
0,
c.human_population
)
},
order_by: [
# based on `AS total_population` in the previous fragment
{:desc, fragment("total_population")}
]
)
Expressions examples
City |> order_by([c], asc: c.name, desc: c.population)
City |> order_by(asc: :name) # Sorts by the cities name
City |> order_by(^order_by_param) # Keyword list
Preloads the associations into the result set.
Imagine you have a schema Post
with a has_many :comments
association and you execute the following query:
Repo.all from p in Post, preload: [:comments]
The example above will fetch all posts from the database and then do
a separate query returning all comments associated with the given posts.
The comments are then processed and associated to each returned post
under the comments
field.
Often times, you may want posts and comments to be selected and filtered in the same query. For such cases, you can explicitly tell an existing join to be preloaded into the result set:
Repo.all from p in Post,
join: c in assoc(p, :comments),
where: c.published_at > p.updated_at,
preload: [comments: c]
In the example above, instead of issuing a separate query to fetch
comments, Ecto will fetch posts and comments in a single query and
then do a separate pass associating each comment to its parent post.
Therefore, instead of returning number_of_posts * number_of_comments
results, like a join
would, it returns only posts with the comments
fields properly filled in.
Nested associations can also be preloaded in both formats:
Repo.all from p in Post,
preload: [:author, comments: :likes]
Repo.all from p in 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}]
Preload queries
Preload also allows queries to be given, allowing you to filter or customize how the preloads are fetched:
comments_query = from c in Comment, order_by: c.published_at
Repo.all from p in Post, preload: [comments: ^comments_query]
The example above will issue two queries, one for loading posts and
then another for loading the comments associated with the posts.
Comments will be ordered by published_at
.
When specifying a preload query, you can still nest preloads. For instance, you could preload an author's published posts and their comments as follows:
posts_query = from p in Post, where: p.state == :published
Repo.all from a in Author, preload: [posts: ^{posts_query, [:comments]}]
If you prefer, you can also add additional preloads directly in the
posts_query
:
posts_query =
from p in Post, where: p.state == :published, preload: :related_posts
Note: keep in mind operations like limit and offset in the preload query will affect the whole result set and not each association. For example, the query below:
comments_query = from c in Comment, order_by: c.popularity, limit: 5
Repo.all from p in Post, preload: [comments: ^comments_query]
won't bring the top of comments per post. Rather, it will only bring the 5 top comments across all posts. Instead, you must use a window:
ranking_query =
from c in Comment,
select: %{id: c.id, row_number: over(row_number(), :posts_partition)},
windows: [posts_partition: [partition_by: :post_id, order_by: :popularity]]
comments_query =
from c in Comment,
join: r in subquery(ranking_query),
on: c.id == r.id and r.row_number <= 5
Repo.all from p in Post, preload: [comments: ^comments_query]
For :through
associations, such as a post may have many comments_authors,
written as has_many :comments_authors, through: [:comments, :author]
the query given to preload customizes the relationship between comments and
authors, even if preloaded through posts. Another way to put it, in case of
:through
associations, the query given to preload customizes the last join
of the association chain. This means order_by
clauses on :through
associations affect only the direct relationship between comments
and
authors
, not between posts and comments.
Preload functions
Preload also allows functions to be given. If the function has an arity of 1, it receives only the IDs of the parent association. If it has an arity of 2, it receives the IDS of the parent association as the first argument and the association metadata as the second argument. Both functions must return the associated data. Ecto then will map this data and sort it by the relationship key:
comment_preloader = fn post_ids -> fetch_comments_by_post_ids(post_ids) end
Repo.all from p in Post, preload: [comments: ^comment_preloader]
This is useful when the whole dataset was already loaded or must be explicitly fetched from elsewhere. The IDs received by the preloading function and the result returned depends on the association type:
For
has_many
andbelongs_to
- the function receives the IDs of the parent association and it must return a list of maps or structs with the associated entries. The associated map/struct must contain the "foreign_key" field. For example, if a post has many comments, when preloading the comments with a custom function, the function will receive a list of "post_ids" as the argument and it must return maps or structs representing the comments. The maps/structs must include the:post_id
fieldFor
has_many :through
- it behaves similarly to a regularhas_many
but note that the IDs received are of the last association. Imagine, for example, a post has many comments and each comment has an author. Therefore, a post may have many comments_authors, written ashas_many :comments_authors, through: [:comments, :author]
. When preloading authors with a custom function via:comments_authors
, the function will receive the IDs of the authors as the last stepFor
many_to_many
- the function receives the IDs of the parent association and it must return a tuple with the parent id as the first element and the association map or struct as the second. For example, if a post has many tags, when preloading the tags with a custom function, the function will receive a list of "post_ids" as the argument and it must return a tuple in the format of{post_id, tag}
The 2-arity version of the function is especially useful if you would like to build a general preloader that works across all associations. For example, if you would like to build a preloader for lateral joins that finds the newest associations you may do the following:
lateral_preloader = fn ids, assoc -> newest_records(ids, assoc, 5) end
def 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
Repo.all(query)
end
For the list of available metadata, see the module documentation of the association types.
For example, see Ecto.Association.BelongsTo
.
Dynamic preloads
Preloads can also be specified dynamically using the dynamic
macro:
preloads = [comments: dynamic([comments: c], c)]
Repo.all from p in Post,
join: c in assoc(p, :comments),
as: :comments,
where: c.published_at > p.updated_at,
preload: ^preloads
See dynamic/2
for more information.
Keywords example
# Returns all posts, their associated comments, and the associated
# likes for those comments.
from(p in Post,
preload: [comments: :likes],
select: p
)
Expressions examples
Post |> preload(:comments) |> select([p], p)
Post
|> join(:left, [p], c in assoc(p, :comments))
|> preload([p, c], [:user, comments: c])
|> select([p], p)
An order by query expression that is prepended to existing ones.
Accepts the same input as order_by/3
except the expression will
come before any previously defined order by expression. This only
works with the macro-based query syntax and not the keyword-based
query syntax.
For example, the following will generate a query that orders by human_popluation
and then name
:
City |> order_by([c], c.name) |> prepend_order_by([c], c.human_population)
The corresponding keyword-based syntax will raise an error:
from c in City, order_by: c.name, prepend_order_by: c.human_population
Puts the given prefix in a query.
Enables or disables recursive mode for CTEs.
According to the SQL standard it affects all CTEs in the query, not individual ones.
See with_cte/3
on example of how to build a query with a recursive CTE.
Reverses the ordering of the query.
ASC columns become DESC columns (and vice-versa). If the query
has no order_by
s, it orders by the inverse of the primary key.
Examples
query |> reverse_order() |> Repo.one()
Post |> order(asc: :id) |> reverse_order() == Post |> order(desc: :id)
A select query expression.
Selects which fields will be selected from the schema and any transformations that should be performed on the fields. Any expression that is accepted in a query can be a select field.
Select also allows each expression to be wrapped in lists, tuples or maps as shown in the examples below. A full schema can also be selected.
There can only be one select expression in a query, if the select expression
is omitted, the query will by default select the full schema. If select
is
given more than once, an error is raised. Use exclude/2
if you would like
to remove a previous select for overriding or see select_merge/3
for a
limited version of select
that is composable and can be called multiple
times.
select
also accepts a list of atoms where each atom refers to a field in
the source to be selected.
Keywords examples
from(c in City, select: c) # returns the schema as a struct
from(c in City, select: {c.name, c.population})
from(c in City, select: [c.name, c.county])
from(c in City, select: %{n: c.name, answer: 42})
from(c in City, select: %{c | alternative_name: c.name})
from(c in City, select: %Data{name: c.name})
It is also possible to select a struct and limit the returned fields at the same time:
from(City, select: [:name])
The syntax above is equivalent to:
from(city in City, select: struct(city, [:name]))
You can also write:
from(city in City, select: map(city, [:name]))
If you want a map with only the selected fields to be returned.
To select a struct but omit only given fields, you can
override them with nil
or another default value:
from(city in City, select: %{city | geojson: nil, text: "<redacted>"})
For more information, read the docs for Ecto.Query.API.struct/2
and Ecto.Query.API.map/2
.
Expressions examples
City |> select([c], c)
City |> select([c], {c.name, c.country})
City |> select([c], %{"name" => c.name})
City |> select([:name])
City |> select([c], struct(c, [:name]))
City |> select([c], map(c, [:name]))
City |> select([c], %{c | geojson: nil, text: "<redacted>"})
Dynamic parts
Dynamics can be part of a select
as values in a map that must be interpolated
at the root level:
period = if monthly?, do: dynamic([p], p.month), else: dynamic([p], p.date)
metric = if distance?, do: dynamic([p], p.distance), else: dynamic([p], p.time)
from(c in City, select: ^%{period: period, metric: metric})
Mergeable select query expression.
This macro is similar to select/3
except it may be specified
multiple times as long as every entry is a map. This is useful
for merging and composing selects. For example:
query = from p in Post, select: %{}
query =
if include_title? do
from p in query, select_merge: %{title: p.title}
else
query
end
query =
if include_visits? do
from p in query, select_merge: %{visits: p.visits}
else
query
end
In the example above, the query is built little by little by merging into a final map. If both conditions above are true, the final query would be equivalent to:
from p in Post, select: %{title: p.title, visits: p.visits}
If :select_merge
is called and there is no value selected previously,
it will default to the source, p
in the example above.
The argument given to :select_merge
must always be a map. The value
being merged on must be a struct or a map. If it is a struct, the fields
merged later on must be part of the struct, otherwise an error is raised.
If the argument to :select_merge
is a constructed struct
(Ecto.Query.API.struct/2
) or map (Ecto.Query.API.map/2
) where the source
to struct or map may be a nil
value (as in an outer join), the source will
be returned unmodified.
query =
Post
|> join(:left, [p], t in Post.Translation,
on: t.post_id == p.id and t.locale == ^"en"
)
|> select_merge([_p, t], map(t, ^~w(title summary)a))
If there is no English translation for the post, the untranslated post
title
will be returned and summary
will be nil
. If there is, both
title
and summary
will be the value from Post.Translation
.
select_merge
cannot be used to set fields in associations, as
associations are always loaded later, overriding any previous value.
Dynamics can be part of a select_merge
as values in a map that must be
interpolated at the root level. The rules for merging detailed above apply.
This allows merging dynamic values into previously selected maps and structs.
Converts a query into a subquery.
If a subquery is given, returns the subquery itself.
If any other value is given, it is converted to a query via
Ecto.Queryable
and wrapped in the Ecto.SubQuery
struct.
subquery
is supported in:
from
,join
,where
, in the formp.x in subquery(q)
,select
andselect_merge
, in the form of%{field: subquery(...)}
.
Examples
# Get the average salary of the top 10 highest salaries
query = from Employee, order_by: [desc: :salary], limit: 10
from e in subquery(query), select: avg(e.salary)
A prefix can be specified for a subquery, similar to standard repo operations:
query = from Employee, order_by: [desc: :salary], limit: 10
from e in subquery(query, prefix: "my_prefix"), select: avg(e.salary)
Subquery can also be used in a join
expression.
UPDATE posts
SET sync_started_at = $1
WHERE id IN (
SELECT id FROM posts
WHERE synced = false AND (sync_started_at IS NULL OR sync_started_at < $1)
LIMIT $2
)
We can write it as a join expression:
subset = from(p in Post,
where: p.synced == false and
(is_nil(p.sync_started_at) or p.sync_started_at < ^min_sync_started_at),
limit: ^batch_size
)
Repo.update_all(
from(p in Post, join: s in subquery(subset), on: s.id == p.id),
set: [sync_started_at: NaiveDateTime.utc_now()]
)
Or as a where
condition:
subset_ids = from(p in subset, select: p.id)
Repo.update_all(
from(p in Post, where: p.id in subquery(subset_ids)),
set: [sync_started_at: NaiveDateTime.utc_now()]
)
If you need to refer to a parent binding which is not known when writing the subquery,
you can use parent_as
as shown in the examples under "Named bindings"
in this module doc.
You can also use subquery directly in select
and select_merge
:
comments_count = from(c in Comment, where: c.post_id == parent_as(:post).id, select: count())
from(p in Post, as: :post, select: %{id: p.id, comments: subquery(comments_count)})
A union query expression.
Combines result sets of multiple queries. The select
of each query
must be exactly the same, with the same types in the same order.
Union expression returns only unique rows as if each query returned
distinct results. This may cause a performance penalty. If you need
to combine multiple result sets without removing duplicate rows
consider using union_all/2
.
Combination behaviour
There are several behaviours of combination queries that must be taken into account, otherwise you may unexpectedly return the wrong query result.
Order by, limit and offset
The order_by
, limit
and offset
expressions of the parent query apply
to the result of the entire combination. order_by
must be specified in one
of the following ways, since the combination of two or more queries is not
automatically aliased:
- Use
Ecto.Query.API.fragment/1
to pass anorder_by
statement that directly access the combination fields. - Wrap the combination in a subquery and refer to the binding of the subquery.
Column selection ordering
The columns of each of the queries in the combination must be specified in the exact same order. Otherwise, you may see the values of one column appearing in another. This holds for all types of select expressions, including maps.
For example, the following query will interchange the values of the supplier's name and city because that is the order the fields are specified in the customer query.
supplier_query = from s in Supplier, select: %{city: s.city, name: s.name}
customer_query = from c in Customer, select: %{name: c.name, city: c.city}
union(supplier_query, ^customer_query)
Selecting literal atoms
When selecting a literal atom, its value must be the same across all queries. Otherwise, the value from the parent query will be applied to all other queries. This also holds true for selecting maps with atom keys.
Keywords examples
# Unordered result
supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, union: ^supplier_query
# Ordered result
supplier_query = from s in Supplier, select: s.city
union_query = from c in Customer, select: c.city, union: ^supplier_query
from s in subquery(union_query), order_by: s.city
Expressions examples
# Unordered result
supplier_query = Supplier |> select([s], s.city)
Customer |> select([c], c.city) |> union(^supplier_query)
# Ordered result
customer_query = Customer |> select([c], c.city) |> order_by(fragment("city"))
supplier_query = Supplier |> select([s], s.city)
union(customer_query, ^supplier_query)
A union all query expression.
Combines result sets of multiple queries. The select
of each query
must be exactly the same, with the same types in the same order.
Combination behaviour
There are several behaviours of combination queries that must be taken into account, otherwise you may unexpectedly return the wrong query result.
Order by, limit and offset
The order_by
, limit
and offset
expressions of the parent query apply
to the result of the entire combination. order_by
must be specified in one
of the following ways, since the combination of two or more queries is not
automatically aliased:
- Use
Ecto.Query.API.fragment/1
to pass anorder_by
statement that directly access the combination fields. - Wrap the combination in a subquery and refer to the binding of the subquery.
Column selection ordering
The columns of each of the queries in the combination must be specified in the exact same order. Otherwise, you may see the values of one column appearing in another. This holds for all types of select expressions, including maps.
For example, the following query will interchange the values of the supplier's name and city because that is the order the fields are specified in the customer query.
supplier_query = from s in Supplier, select: %{city: s.city, name: s.name}
customer_query = from c in Customer, select: %{name: c.name, city: c.city}
union_all(supplier_query, ^customer_query)
Selecting literal atoms
When selecting a literal atom, its value must be the same across all queries. Otherwise, the value from the parent query will be applied to all other queries. This also holds true for selecting maps with atom keys.
Keywords examples
# Unordered result
supplier_query = from s in Supplier, select: s.city
from c in Customer, select: c.city, union_all: ^supplier_query
# Ordered result
supplier_query = from s in Supplier, select: s.city
union_all_query = from c in Customer, select: c.city, union_all: ^supplier_query
from s in subquery(union_all_query), order_by: s.city
Expressions examples
# Unordered result
supplier_query = Supplier |> select([s], s.city)
Customer |> select([c], c.city) |> union_all(^supplier_query)
# Ordered result
customer_query = Customer |> select([c], c.city) |> order_by(fragment("city"))
supplier_query = Supplier |> select([s], s.city)
union_all(customer_query, ^supplier_query)
An update query expression.
Updates are used to update the filtered entries. In order for
updates to be applied, Ecto.Repo.update_all/3
must be invoked.
Keywords example
from(u in User, update: [set: [name: "new name"]])
Expressions examples
User |> update([u], set: [name: "new name"])
User |> update(set: [name: "new name"])
Interpolation
new_name = "new name"
from(u in User, update: [set: [name: ^new_name]])
new_name = "new name"
from(u in User, update: [set: [name: fragment("upper(?)", ^new_name)]])
Operators
The update expression in Ecto supports the following operators:
set
- sets the given field in the table to the given valuefrom(u in User, update: [set: [name: "new name"]])
inc
- increments (or decrements if the value is negative) the given field in the table by the given valuefrom(u in User, update: [inc: [accesses: 1]])
push
- pushes (appends) the given value to the end of the array fieldfrom(u in User, update: [push: [tags: "cool"]])
pull
- pulls (removes) the given value from the array fieldfrom(u in User, update: [pull: [tags: "not cool"]])
Composable
Remember that all query expressions are composable, so you can use update
multiple times in the same query to merge the update expressions:
new_name = "new name"
User
|> update([u], set: [name: fragment("upper(?)", ^new_name)])
|> update([u], set: [age: 42])
This can be useful to compose updates from different functions
or when mixing interpolation, such as set: ^updates
, with regular
query expressions, such as set: [age: u.age + 1]
.
An AND where query expression.
where
expressions are used to filter the result set. If there is more
than one where expression, they are combined with an and
operator. All
where expressions have to evaluate to a boolean value.
where
also accepts a keyword list where the field given as key is going to
be compared with the given value. The fields will always refer to the source
given in from
.
Keywords example
from(c in City, where: c.country == "Sweden")
from(c in City, where: [country: "Sweden"])
It is also possible to interpolate the whole keyword list, allowing you to dynamically filter the source:
filters = [country: "Sweden"]
from(c in City, where: ^filters)
Expressions examples
City |> where([c], c.country == "Sweden")
City |> where(country: "Sweden")
Defines windows which can be used with Ecto.Query.WindowAPI
.
Receives a keyword list where keys are names of the windows and values are a keyword list with window expressions.
Examples
# Compare each employee's salary with the average salary in his or her department
from e in Employee,
select: {e.depname, e.empno, e.salary, over(avg(e.salary), :department)},
windows: [department: [partition_by: e.depname]]
In the example above, we get the average salary per department.
:department
is the window name, partitioned by e.depname
and avg/1
is the window function. For more information
on windows functions, see Ecto.Query.WindowAPI
.
Window expressions
The following keys are allowed when specifying a window.
:partition_by
A list of fields to partition the window by, for example:
windows: [department: [partition_by: e.depname]]
A list of atoms can also be interpolated for dynamic partitioning:
fields = [:depname, :year]
windows: [dynamic_window: [partition_by: ^fields]]
:order_by
A list of fields to order the window by, for example:
windows: [ordered_names: [order_by: e.name]]
It works exactly as the keyword query version of order_by/3
.
:frame
A fragment which defines the frame for window functions.
Examples
# Compare each employee's salary for each month with his average salary for previous 3 months
from p in Payroll,
select: {p.empno, p.date, p.salary, over(avg(p.salary), :prev_months)},
windows: [prev_months: [partition_by: p.empno, order_by: p.date, frame: fragment("ROWS 3 PRECEDING EXCLUDE CURRENT ROW")]]
A common table expression (CTE) also known as WITH expression.
name
must be a compile-time literal string that is being used
as the table name to join the CTE in the main query or in the
recursive CTE.
IMPORTANT! Beware of using CTEs. In raw SQL, CTEs can be used as a mechanism to organize queries, but said mechanism has no purpose in Ecto since Ecto queries are composable by definition. In other words, if you need to break a large query into parts, use all of the functionality in Elixir and in this module to structure your code. Furthermore, breaking a query into CTEs can negatively impact performance, as the database may not optimize efficiently across CTEs. The main use case for CTEs in Ecto is to provide recursive definitions, which we outline in the following section. Non-recursive CTEs can often be written as joins or subqueries, which provide better performance.
Options
:as
- the CTE query itself or a fragment:materialized
- a boolean indicating whether the CTE should be materialized. If blank, the database's default behaviour will be used (only supported by Postgrex, for the built-in adapters):operation
- one of:all
,:update_all
, or:delete_all
indicating the operation type of the CTE query. If blank, it defaults to:all
, making the CTE query a SELECT query. (only supported by Postgres built-in adapter)
Recursive CTEs
Use recursive_ctes/2
to enable recursive mode for CTEs.
In the CTE query itself use the same table name to leverage
recursion that has been passed to the name
argument. Make sure
to write a stop condition to avoid an infinite recursion loop.
Generally speaking, you should only use CTEs in Ecto for
writing recursive queries.
Expression examples
Products and their category names for breadcrumbs:
category_tree_initial_query =
Category
|> where([c], is_nil(c.parent_id))
category_tree_recursion_query =
Category
|> join(:inner, [c], ct in "category_tree", on: c.parent_id == ct.id)
category_tree_query =
category_tree_initial_query
|> union_all(^category_tree_recursion_query)
Product
|> recursive_ctes(true)
|> with_cte("category_tree", as: ^category_tree_query)
|> join(:left, [p], c in "category_tree", on: c.id == p.category_id)
|> group_by([p], p.id)
|> select([p, c], %{p | category_names: fragment("ARRAY_AGG(?)", c.name)})
It's also possible to pass a raw SQL fragment:
@raw_sql_category_tree """
SELECT * FROM categories WHERE c.parent_id IS NULL
UNION ALL
SELECT * FROM categories AS c, category_tree AS ct WHERE ct.id = c.parent_id
"""
Product
|> recursive_ctes(true)
|> with_cte("category_tree", as: fragment(@raw_sql_category_tree))
|> join(:inner, [p], c in "category_tree", on: c.id == p.category_id)
You can also query over the CTE table itself. In such cases, you can pass tuple with the CTE table name as the first element and an Ecto schema as the second element. This will cast the result rows to Ecto structs as long as the Ecto schema maps over the same fields in the CTE table:
{"category_tree", Category}
|> recursive_ctes(true)
|> with_cte("category_tree", as: ^category_tree_query)
|> join(:left, [c], p in assoc(c, :products))
|> group_by([c], c.id)
|> select([c, p], %{c | products_count: count(p.id)})
Keep in mind that the query above will inherit all properties from the Category
schema,
include a @schema_prefix
if any is set. In such cases, you can disable those properties
by setting them as option:
from(cte in {"category_tree", Category}, prefix: nil)
|> recursive_ctes(true)
|> with_cte("category_tree", as: ^category_tree_query)
For Postgres built-in adapter, it is possible to define data-modifying CTE queries:
update_categories_query =
Category
|> where([c], is_nil(c.parent_id))
|> update([c], set: [name: "Root category"])
|> select([c], c)
{"update_categories", Category}
|> with_cte("update_categories", as: ^update_categories_query, operation: :update_all)
|> select([c], c)
Note: In order to retrieve the updates rows from a CTE query, the parent query
must select rows from the CTE table instead of the table referenced by the CTE query.
For example, "update_categories"
will return updates rows for "category"
table, but
selecting from "category"
table directly will return unaffected rows.
For more details see Postgres documentation on data-modifying CTEs and how these work
with snapshots.
Keyword syntax is not supported for this feature.
Limitation: CTEs on schemas with source fields
Ecto allows developers to say that a table in their Ecto schema maps to a different column in their database:
field :group_id, :integer, source: :iGroupId
At the moment, using a schema with source fields in CTE may emit invalid queries. If you are running into such scenarios, your best option is to use a fragment as your CTE.
Applies a callback function to a query if it doesn't contain the given named binding. Otherwise, returns the original query.
The callback function must accept a queryable and return an Ecto.Query
struct
that contains the provided named binding, otherwise an error is raised. It can also
accept second argument which is the atom representing the name of a binding.
For example, one might use this function as a convenience to conditionally add a new named join to a query:
if has_named_binding?(query, :comments) do
query
else
join(query, :left, [p], c in assoc(p, :comments), as: :comments)
end
With this function it can be simplified to:
with_named_binding(query, :comments, fn query, binding ->
join(query, :left, [p], a in assoc(p, ^binding), as: ^binding)
end)
For more information on named bindings see "Named bindings"
in this module doc or has_named_binding?/2
.
Enables or disables ties for limit expressions.
If there are multiple records tied for the last position in an ordered
limit result, setting this value to true
will return all of the tied
records, even if the final result exceeds the specified limit.
Must be a boolean or evaluate to a boolean at runtime. Can only be applied
to queries with a limit
expression or an error is raised. If limit
is redefined then with_ties
must be reapplied.
Not all databases support this option and the ones that do might list it
under the FETCH
command. Databases may require a corresponding order_by
statement to evaluate ties.
Keywords example
from(p in Post, where: p.author_id == ^current_user, order_by: [desc: p.visits], limit: 10, with_ties: true)
Expressions example
Post |> where([p], p.author_id == ^current_user) |> order_by([p], desc: p.visits) |> limit(10) |> with_ties(true)