Ecto v2.0.0-beta.2 Ecto.Query

Provides the Query DSL.

Queries are used to retrieve and manipulate data in a repository (see Ecto.Repo). Although this module provides a complete API, supporting expressions like where/3, select/3 and so forth, most of the time developers need to import only the from/2 macro.

# Imports only from/2 of Ecto.Query
import Ecto.Query, only: [from: 2]

# Create a query
query = from w in Weather,
          where: w.prcp > 0,
          select: w.city

# Send the query to the repository
Repo.all(query)

Query expressions

Ecto allows a limited set of expressions inside queries. In the query below, for example, we use w.prcp to access a field, the > comparison operator and the literal 0:

query = from w in Weather, where: w.prcp > 0

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

External values and Elixir expressions can be injected into a query expression with ^:

def with_minimum(age, height_ft) do
  from u in User,
    where: u.age > ^age and u.height > ^(height_ft * 3.28)
end

with_minimum(18, 5.0)

Interpolation can also be used with the field/2 function which allows developers to dynamically choose a field to query:

def has_four(doors_or_tires) do
  from c in Car,
    where: field(c, ^doors_or_tires) == 4
end

In the example above, both has_four(:doors) and has_four(:tires) would be valid calls as the field is dynamically inserted.

Casting

Ecto is able to cast interpolated values in queries:

age = "1"
Repo.all(from u in User, where: u.age > ^age)

The example above works because u.age is tagged as an :integer in the User schema and therefore Ecto will attempt to cast the interpolated ^age to integer. When a value cannot be cast, Ecto.CastError is raised.

Composition

Ecto queries are composable. For example, the query above can actually be defined in two parts:

# Create a query
query = from w in Weather, where: w.prcp > 0

# Extend the query
query = from w in query, select: w.city

Composing queries uses the same syntax as creating a query. The difference is that, instead of passing a schema like Weather on the right side of in, we passed the query itself.

Any value can be used on the right-side of in as long as it implements the Ecto.Queryable protocol. For example, the queryable protocol is also implemented for strings, which allows a query to run directly against a table, without defining an Ecto.Schema:

from w in "weather", where: w.prcp > 0, select: w.city

When writing queries directly against a table, without a schema, you need to consider two limitations:

  1. You must always specify the select clause as Ecto requires fields to be explicitly listed for schemaless queries. When you have a schema, the fields are retrieved from the schema

  2. Because there isn’t a schema, Ecto can’t automatically cast interpolated values nor the values returned by the database

In any case, regardless if a schema has been given or not, Ecto queries are always composable thanks to its binding system.

Query bindings

On the left side of in we specify the query bindings. This is done inside from and join clauses. In the query below w is a binding and w.prcp is a field access using this binding.

query = from w in Weather, where: w.prcp > 0

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 above query we again need to tell Ecto what bindings to expect:

query = from w in query, select: w.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 use 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, where: 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 bindings are position based, we will always sort by the inserted_at column from the from source.

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: take(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 a 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 “downcase(?)” 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("downcase(?)", 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 w in Weather, where: w.prcp > 0, select: w.city

Remember that keywords is a syntax sugar in Elixir for passing a list of two-item tuples where the first element is an atom. The code above is equivalent to:

from(w in Weather, [{:where, w.prcp > 0}, {:select, w.city}])

Due to the prevalence of the pipe operator in Elixir, Ecto also supports a pipe-based syntax:

from(w in Weather)
|> where([w], w.prcp > 0)
|> select([w], w.city)

The keyword-based and pipe-based examples are equivalent.

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.

To set the prefix on a query:

results =
  query # May be User or an Ecto.Query itself
  |> Ecto.Queryable.to_query
  |> Map.put(:prefix, "foo")
  |> Repo.all

When a prefix is set in a query, all loaded structs will belong to that prefix, so operations like update and delete will be applied to the proper prefix. In case you want to manually set the prefix for new data, specially on insert, use Ecto.put_meta/2.

Summary

Functions

Resets a previously set field on a query

Restricts the query to return the first result ordered by primary key

Restricts the query to return the last result ordered by primary key

Converts a query into a subquery

Macros

A distinct query expression

Creates a query

A group by query expression

A having query expression

A limit query expression

A lock query expression

An offset query expression

An order by query expression

Preloads the associations into the given struct

A select query expression

An update query expression

A where query expression

Types

t

Functions

exclude(query, field)

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)
first(queryable, order_by \\ nil)

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
last(queryable, order_by \\ nil)

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

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)

Macros

distinct(query, binding \\ [], expr)

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, the row that is being kept depends on the ordering of the rows. When an order_by expression is also added to the query, all fields in the distinct expression are automatically referenced order_by too.

distinct also accepts a list of atoms where each atom refers to a field in source.

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

# Using atoms
from(p in Post, distinct: :category, order_by: :date)

Expressions example

Post
|> distinct(true)
|> order_by([p], [p.category, p.author])
from(expr, kw \\ [])

Creates a query.

It can either be a keyword query or a query expression. If it is a keyword query the first argument should be an in expression and the second argument a keyword query where the keys are expression types and the values are expressions.

If it is a query expression the first argument is the original query and the second argument the expression.

Keywords example

from(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 such. However, extending a 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_a))
end

Notice we have created a p variable to represent each item in the query. When the given query has more than one from expression, 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.

group_by(query, binding \\ [], expr)

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

# Group on all fields on the Post schema
from(p in Post, group_by: p, select: p)

# 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))
having(query, binding \\ [], expr)

A 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))
join(query, qual, binding \\ [], expr, on \\ nil)

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 or :full. For a keyword query the :join keyword can be changed to: :inner_join, :left_join, :right_join or :full_join.

Currently it is possible to join on an Ecto.Schema (a module), an existing source (a binary representing a table), an association or a fragment. See the examples below.

Keywords examples

from c in Comment,
  join: p in Post, on: c.post_id == p.id,
  select: {p.title, c.text}

from p in Post,
  left_join: c in assoc(p, :comments),
  select: {p, c}

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

Joining with fragments

When you need to join on a complex expression that cannot be expressed via Ecto associations, Ecto supports fragments in joins:

Comment
|> join(:inner, [c], p in fragment("SOME COMPLEX QUERY", c.id, ^some_param))

This style discouraged due to its complexity.

limit(query, binding \\ [], expr)

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)
lock(query, expr)

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")
offset(query, binding \\ [], expr)

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)
order_by(query, binding \\ [], expr)

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.

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
preload(query, bindings \\ [], expr)

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

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 |> preload([p, c], [:user, comments: c]) |> select([p], p)
select(query, binding \\ [], expr)

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.

The sub-expressions in the query can be wrapped in lists, tuples or maps as shown in the examples. 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.

select also accepts a list of atoms where each atom refers to a field in 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: {c.name, ^to_string(40 + 2), 43})
from(c in City, select: %{n: c.name, answer: 42})

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: take(city, [:name]))

For more information, read the docs for Ecto.Query.API.take/2.

Expressions examples

City |> select([c], c)
City |> select([c], {c.name, c.country})
City |> select([c], %{"name" => c.name})
update(query, binding \\ [], expr)

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

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"]])
where(query, binding \\ [], expr)

A 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.state == "Sweden")
from(c in City, where: [state: "Sweden"])

It is also possible to interpolate the whole keyword list, allowing you to dynamically filter the source:

filters = [state: "Sweden"]
from(c in City, where: ^filters)

Expressions example

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