Ecto v0.14.3 → Overview → Ecto → Query

Ecto.Query

Summary
Types
Functions
Macros

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 times developers need to import only the from/2 macro.

# Imports only from/2 from 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)

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

Keep in mind though the variable names used on the left-hand side of in are just a convenience, they are not taken into account in the query generation.

Any value can be used on the right-side of in as long as it implements the Ecto.Queryable protocol.

Query expressions

Ecto allows a limitted set of expressions to be used inside queries:

Comparison operators: ==, !=, <=, >=, <, >
Boolean operators: and, or, not
Inclusion operator: in/2
Search functions: like/2 and ilike/2
Null check functions: is_nil/1
Aggregates: count/1, avg/1, sum/1, min/1, max/1
Date/time intervals: datetime_add/3, date_add/3

Futhermore, Ecto allows the following literals inside 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 must be passed as a parameter using interpolation 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 at_least_four(doors_or_tires) do
    from c in Car,
  where: field(c, ^doors_or_tires) >= 4
end

In the example above, both at_least_four(:doors) and at_least_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 model and therefore Ecto will attempt to cast the interpolated ^age to integer. In case a value cannot be cast, Ecto.CastError is raised.

In some situations, Ecto is unable to infer the type for interpolated values (as a database would be unable) and you may need to explicitly tag it with the type/2 function:

type(^"1", :integer)
type(^<<0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15>>, Ecto.UUID)

It is important to keep in mind that Ecto cannot cast nil values in queries. Passing nil automatically causes the query to fail.

Date/time intervals

In Ecto, it is possible to perform interval based operation on both date and datetime as long it is supported by the underlying storage:

# Get all items published since the last month
from p in Post, where: p.published_at >
                       datetime_add(^Ecto.DateTime.utc, -1, "month")

In the example above, we used datetime_add/3 to subtract one month from the current datetime and compared it with the p.published_at. If you want to perform operations on date, date_add/3 could be used.

The following intervals are supported: year, month, week, day, hour, minute, second, millisecond and microsecond.

Query expressions

In all examples so far, we have used the keywords query syntax to create a query. Our first example:

import Ecto.Query

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

Simply expands to the following query expressions:

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

Which then expands to:

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

This module documents each of those macros, providing examples both in the keywords query and in the query expression formats.

Fragments

It is not possible to represent all possible queries in Ecto’s query syntax. When such is required, it is possible to use fragments to send any expression to the database:

def unpublished_by_title(title) do
  from p in Post,
    where: is_nil(p.published_at) and
           fragment("downcase(?)", p.title) == ^title
end

In the example above, we are using the downcase procedure in the database to downcase the title column.

It is very important to keep in mind that Ecto is unable to do any type casting described above when fragments are used. You can however use the type/2 function to give Ecto some hints:

fragment("downcase(?)", p.title) == type(^title, :string)

Or even say the right side is of the same type as p.title:

fragment("downcase(?)", p.title) == type(^title, p.title)

Source

Summary↑

distinct(query, binding \\ [], expr)	A distinct query expression
exclude(query, field)	Resets a previously set field on a query
from(expr, kw \\ [])	Creates a query
group_by(query, binding, expr)	A group by query expression
having(query, binding, expr)	A having query expression
join(query, qual, binding, expr, on \\ nil)	A join query expression
limit(query, binding, expr)	A limit query expression
lock(query, expr)	A lock query expression
offset(query, binding, expr)	An offset query expression
order_by(query, binding, expr)	An order by query expression
preload(query, bindings \\ [], expr)	Preloads the associations into the given model
select(query, binding, expr)	A select query expression
update(query, binding, expr)	An update query expression
where(query, binding, expr)	A where query expression

Types ↑

Functions

exclude(query, field)

(function) # ↑

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)

Source

Macros

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

(macro) # ↑

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.

Keywords examples

# Returns the list of different categories in the Post model
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])

Expressions examples

Post
|> distinct(true)
|> order_by([p], [p.category, p.author])

Source

from(expr, kw \\ [])

(macro) # ↑

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 they 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 examples

from(City, select: c)

Expressions examples

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 none of limit and offset requires such. However, extending a query with where expression would require so:

def published(query) do
  from p in query, where: p.published_at != nil
end

Notice we have created a p variable to represent each item in the query. In case the given query has more than one from expression, each of them must be given in the order they were bound:

def published_multi(query) do
  from [p,o] in query,
  where: p.published_at != nil and o.published_at != nil
end

Note the variables p and o must be named as you find more convenient as they have no importance in the query sent to the database.

Source

group_by(query, binding, expr)

(macro) # ↑

A group by query expression.

Groups together rows from the model 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.

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 model
from(p in Post,
  group_by: p,
  select: p)

Expressions examples

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

Source

having(query, binding, expr)

(macro) # ↑

A having query expression.

Like where having filters rows from the model, 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 examples

# 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 examples

Post
|> group_by([p], p.category)
|> having([p], avg(p.num_comments) > 10)
|> select([p], count(p.id))

Source

join(query, qual, binding, expr, on \\ nil)

(macro) # ↑

A join query expression.

Receives a model that is to be joined to the query and a condition to do the joining on. 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.

The join condition can be automatically set when doing an association join. An association join can be done on any association field (has_many, has_one, belongs_to).

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

Source

limit(query, binding, expr)

(macro) # ↑

A limit query expression.

Limits the number of rows selected from the result. Can be any expression but have to evaluate to an integer value and it can’t include any field.

If limit is given twice, it overrides the previous value.

Keywords examples

from(u in User, where: u.id == ^current_user, limit: 1)

Expressions examples

User |> where([u], u.id == ^current_user) |> limit([u], 1)

Source

lock(query, expr)

(macro) # ↑

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.Model.OptimisticLock module.

Keywords examples

from(u in User, where: u.id == ^current_user, lock: "FOR SHARE NOWAIT")

Expressions examples

User |> where(u.id == ^current_user) |> lock("FOR SHARE NOWAIT")

Source

offset(query, binding, expr)

(macro) # ↑

An offset query expression.

Offsets the number of rows selected from the result. Can be any expression but have to evaluate to an integer value and it can’t include any field.

If offset is given twice, it overrides the previous value.

Keywords examples

# Get all posts on page 4
from(p in Post, limit: 10, offset: 30)

Expressions examples

Post |> limit([p], 10) |> offset([p], 30)

Source

order_by(query, binding, expr)

(macro) # ↑

An order by query expression.

Orders the fields based on one or more fields. It accepts a single field or a list field, the direction can be specified in a keyword list as shown in the examples. There can be several order by expressions in a query.

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

Expressions examples

City |> order_by([c], asc: c.name, desc: c.population)

Atom values

For simplicity, order_by also allows the fields to be given as atoms. In such cases, the field always applies to the source given in from (i.e. the first binding). For example, the two expressions below are equivalent:

from(c in City, order_by: [asc: :name, desc: :population])
from(c in City, order_by: [asc: c.name, desc: c.population])

A keyword list can also be interpolated:

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

Source

preload(query, bindings \\ [], expr)

(macro) # ↑

Preloads the associations into the given model.

Preloading allow developers to specify associations that are preloaded into the model. 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 model:

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 though both formats cannot be nested arbitrary. 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, allow 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 to the posts, where they 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 examples

# Returns all posts and their associated 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)

Source

select(query, binding, expr)

(macro) # ↑

A select query expression.

Selects which fields will be selected from the model and any transformations that should be performed on the fields. Any expression that is accepted in a query can be a select field.

There can only be one select expression in a query, if the select expression is omitted, the query will by default select the full model.

The sub-expressions in the query can be wrapped in lists, tuples or maps as shown in the examples. A full model can also be selected. Note that map keys can only be atoms, binaries, integers or floats otherwise an Ecto.Query.CompileError exception is raised at compile-time.

Keywords examples

from(c in City, select: c) # selects the entire model
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_binary(40 + 2), 43})
from(c in City, select: %{n: c.name, answer: 42})

Expressions examples

City |> select([c], c)
City |> select([c], {c.name, c.country})
City |> select([c], %{"name" => c.name})

Source

update(query, binding, expr)

(macro) # ↑

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 examples

from(u in User, update: [set: [name: "new name"]]

Expressions examples

User |> update([u], set: [name: "new name"])

Operators

The update expression in Ecto supports the following operators:

set - sets the given field in table to the given value
```
from(u in User, update: [set: [name: "new name"]]
```
inc - increments the given field in 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"]]
```

Source

where(query, binding, expr)

(macro) # ↑

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 and operator. All where expression have to evaluate to a boolean value.

Keywords examples

from(c in City, where: c.state == "Sweden")

Expressions examples

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

Source