Ecto v0.2.5 → Overview → Ecto → Query

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

# Imports only from/1 and 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.

Data security

External values and elixir expressions can be injected into a query expression with ^. Anything that isn’t inside a ^ expression is treated as a query expression.

This allows one to create dynamic queries:

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

In the example above, we will compare against the age and height given as arguments, appropriately converting the height. Note all external values will be quoted to avoid SQL injection attacks in the underlying repository.

Notice the select clause is optional, Ecto will automatically infers and returns the user record (similar to select: u) from the query above.

Type safety

Ecto queries are also type-safe. For example, the following query:

from u in User, where: u.age == "zero"

will return an error with the following message:

** (Ecto.Query.TypeCheckError) the following expression does not type check:

    &0.age() == "zero"

Allowed types for ==/2:

    number == number
    var == var
    nil == _
    _ == nil

Got: integer == string

The types above mean:

All operations allowed in a query with their respective type are defined in Ecto.Query.API.

Query expansion

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.

Source

Summary
↑

distinct(query, binding, expr)

A distinct query expression
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, expr)

A limit query expression
lock(query, expr)

A lock query expression
offset(query, expr)

An offset query expression
order_by(query, binding, expr)

An order by query expression
preload(query, expr)

Mark associations to be pre-loaded
select(query, binding, expr)

A select query expression
where(query, binding, expr)

A where query expression

Macros

distinct(query, binding, expr)
(macro) # ↑

A distinct query expression.

Only keep one row for each combination of values in the distinct query expression.

The row that is being kept depends on the ordering of the rows. To ensure results are consistent, if an order_by expression is also added to the query, its leftmost part must first reference all the fields in the distinct expression before referencing another field.

Keywords examples

# Returns the list of different categories in the Post model
from(p in Post, distinct: p.category)

# Returns the first (by date) for each different categories of Post
from(p in Post,
   distinct: p.category,
   order_by: [p.category, p.date])

Expressions examples

Post
|> distinct([p], p.category)
|> 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 q 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, belong_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 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 p.comments)
|> select([p, c], {p, c})
Source
limit(query, 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.id == current_user) |> limit(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. Can be any expression but have to evaluate to a boolean value or a string and it can’t include any field.

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

Keywords examples

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

Expressions examples

User |> where(u.id == current_user) |> lock(true)
User |> where(u.id == current_user) |> lock("FOR SHARE NOWAIT")
Source
offset(query, 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(10) |> offset(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)
Source
preload(query, expr)
(macro) # ↑

Mark associations to be pre-loaded.

Pre-loading allow developers to specify associations that should be pre- loaded once the first result set is retrieved. 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.

Nested associations can also be preloaded as seen in the examples below. One query per association to be preloaded will be issued to the database.

Keywords examples

# Returns all posts and their associated comments
from(p in Post,
  preload: [:comments],
  select: p)

# Returns all posts and their associated comments
# with the associated author
from(p in Post,
  preload: [user: [], comments: [:user]],
  select: p)

Expressions examples

Post |> preload(:comments) |> select([p], p)

Post |> preload([:user, {:comments, [:user]}]) |> 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 or tuples as shown in the examples. A full model can also be selected.

The assoc/2 selector can be used to embed an association on a parent model as shown in the examples below. The first argument to assoc has to be a variable bound in the from query expression, the second has to be the field of the association and a variable bound in an association join.

Nested `assoc/2 expressions are also allowed when there are multiple association joins in the query.

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(p in Post, join: c in p.comments, select: assoc(p, comments: c))

# Fetch all posts, their comments and the posts' and comments' authors
      from p in Post,
left_join: p_u in p.author,
left_join: c in p.comments,
left_join: c_u in c.author,
   select: assoc(p, author: p_u, comments: assoc(c, author: c_u))

Expressions examples

City |> select([c], c)
City |> select([c], {c.name, c.country})
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