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

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 an order_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 an order_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)]

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 an order_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 an order_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 as p in Post
• an interpolated Ecto query with zero or more where clauses, such as c 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 to true
• :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.

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}]

Choosing between preloading with joins vs. separate queries

Deciding between preloading associations via joins, a single large query, (preload: [comments: c]) or separate smaller queries (preload: [:comments]) depends on the specific use case. Here are some factors to guide your decision:

• Joins reduce database round trips: By fetching data in a single query, joins can minimize database round trips, potentially reducing overall latency.
• Potential for data duplication: Joins may lead to duplicated data in the result set, which requires more processing by Ecto and consumes more bandwidth when transmitting the results.
• Parallelism with separate queries: When using separate queries outside of a transaction, Ecto can parallelize the preload queries, which can speed up the overall operation.

In general, a good default is to only use joins in preloads if you're already joining the associations in the main query. For example, in the last query in the section above, comments and likes are already joined, so they are included in the preload. However, the author is not joined in the main query, so it is preloaded via a separate query.

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 and belongs_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 field

• For has_many :through - it behaves similarly to a regular has_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 as has_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 step

• For 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
)