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 allows developers to build queries expression bit by bit so they are later interpolated in a query.

Examples

For example, imagine you have a set of conditions you want to build your query on:

dynamic = false

dynamic =
  if params["is_public"] do
    dynamic([p], p.is_public or ^dynamic)
  else
    dynamic
  end

dynamic =
  if params["allow_reviewers"] do
    dynamic([p, a], a.reviewer == true or ^dynamic)
  else
    dynamic
  end

from query, where: ^dynamic

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 inside the 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’

dynamic can be interpolated at the root of a where, having or a join’s on.

For example, the following is forbidden because it is not at the root of a where:

from q in query, where: q.some_condition and ^dynamic

Fortunately that’s easily solvable by simply rewriting it to:

dynamic = dynamic([q], q.some_condition and ^dynamic)
from query, where: ^dynamic

Updates

Dynamic is also supported as each field in an update, for example:

update_to = dynamic([p], p.sum / p.count)
from query, update: [set: [average: ^update_to]]

Resets a previously set field on a query.

It can reset any query field except the query source (from).

Example

query |> Ecto.Query.exclude(:select)

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, or a value that implements the Ecto.Queryable protocol. 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.

Keywords example

from(c in City, select: c)

Expressions example

City |> 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, a variable must be given for each in the order they were bound:

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 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.

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

Expressions example

Post |> group_by([p], p.category) |> select([p], count(p.id))

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

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 join is by default an inner join, the qualifier can be changed by giving the atoms: :inner, :left, :right, :cross, :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, :full_join, :inner_lateral_join or :left_lateral_join.

Currently it is possible to join on:

• an Ecto.Schema, such as p in Post
• an Ecto query with zero or more where clauses, such as from "posts", where: [public: true]
• an association, such as c in assoc(post, :comments)
• a query fragment, such as c in fragment("SOME COMPLEX QUERY")
• a subquery, such as c in subquery(another_query)

The fragment support exists mostly for handling lateral joins. See “Joining with fragments” below.

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 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, 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 join.

Expressions examples

Comment
|> join(:inner, [c], p in Post, 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, 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))

Although using fragments in joins is discouraged in favor of Ecto Query syntax, they are necessary when writing lateral joins as lateral joins require a subquery that refer to previous bindings:

Game
|> join(:inner_lateral, [g], gs in fragment("SELECT * FROM games_sold AS gs WHERE gs.game_id = ? ORDER BY gs.sold_on LIMIT 2", g.id))
|> select([g, gs], {g.name, gs.sold_on})

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.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 shown in the examples.

There can be several order by expressions in a query and new expressions are always appended to the previous 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: :population])

A keyword list can also be interpolated:

values = [asc: :name, desc: :population]
from(c in City, order_by: ^values)

Expressions example

City |> order_by([c], asc: c.name, desc: c.population)
City |> order_by(asc: :name) # Sorts by the cities name

Preloads the associations into the given struct.

Preloading allows developers to specify associations that are preloaded into the struct. Consider this example:

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.

However, often times, you want posts and comments to be selected and filtered in the same query. For such cases, you can explicitly tell the association to be preloaded into the struct:

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.

Nested associations can also be preloaded in both formats:

Repo.all from p in Post,
           preload: [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: [comments: {c, likes: l}]

Keep in mind neither format can be nested arbitrarily. For example, the query below is invalid because we cannot preload likes with the join association c.

Repo.all from p in Post,
           join: c in assoc(p, :comments),
           preload: [comments: {c, :likes}]

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.

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.

Preload functions

Preload also allows functions to be given. In such cases, the function receives the IDs to be fetched and it must return the associated data. This data will then be mapped and sorted:

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.

Keywords example

# Returns all posts, their associated comments, and the associated
# likes for those comments.
from(p in Post,
  preload: [:comments, 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)

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. 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]))

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 left-side of a merge can be a struct or a map. The right side must always be a map. If the left-side is a struct, the fields on the right side must be part of the struct, otherwise an error is raised.

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.

Subqueries are currently only supported in the from and join fields.

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)

Although subqueries are not allowed in WHERE expressions, most subqueries in WHERE expression can be rewritten as JOINs. Imagine you want to write this query:

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
  )

If you attempt to write it as where: p.id in ^subquery(foo), Ecto won’t accept such query. However, the subquery above can be written as a JOIN, which is supported by Ecto. The final Ecto query will look like this:

subset_query = 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_query), on: s.id == p.id),
  set: [sync_started_at: NaiveDateTime.utc_now()]
end)

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 example

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 value

  from(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 value

  from(u in User, update: [inc: [accesses: 1]])

• push - pushes (appends) the given value to the end of the array field

  from(u in User, update: [push: [tags: “cool”]])

• pull - pulls (removes) the given value from the array field

  from(u in User, update: [pull: [tags: “not cool”]])

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 example

City |> where([c], c.country == "Sweden")
City |> where(country: "Sweden")