Ecto v1.1.0 Ecto.Query.API

This module lists all functions allowed in the query API.

Note the functions in this module exist for documentation purposes and one should never need to invoke them directly. Furthermore, it is possible to define your own macros and use them in Ecto queries.

Summary

Functions

Binary != operation

Binary < operation

Binary <= operation

Binary == operation

Binary > operation

Binary >= operation

Binary and operation

Calculates the average for the given entry

Counts the given entry

Counts the distinct values in given entry

Adds a given interval to a date

Adds a given interval to a datetime

Allows a field to be dynamically accessed

Send fragments directly to the database

Searches for search in string in a case insensitive fashion

Checks if the left-value is included in the right one

Checks if the given value is nil

Searches for search in string

Calculates the maximum for the given entry

Calculates the minimum for the given entry

Unary not operation

Binary or operation

Calculates the sum for the given entry

Casts the given value to the given type

Functions

left != right

Binary != operation.

left < right

Binary < operation.

left <= right

Binary <= operation.

left == right

Binary == operation.

left > right

Binary > operation.

left >= right

Binary >= operation.

left and right

Binary and operation.

avg(value)

Calculates the average for the given entry.

from p in Payment, select: avg(p.value)
count(value)

Counts the given entry.

from p in Post, select: count(p.id)
count(value, atom)

Counts the distinct values in given entry.

from p in Post, select: count(p.id, :distinct)
date_add(date, count, interval)

Adds a given interval to a date.

See datetime_add/3 for more information.

datetime_add(datetime, count, interval)

Adds a given interval to a datetime.

The first argument is a datetime, the second one is the count for the interval, which may be either positive or negative and the interval value:

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

field(source, field)

Allows a field to be dynamically accessed.

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

fragment(fragments)

Send fragments directly to the database.

It is not possible to represent all possible database queries using 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)

It is possible to make use of PostgreSQL’s JSON/JSONB data type with fragments, as well:

fragment("?->>? ILIKE ?", p.map, "key_name", ^some_value)

Keyword fragments

In order to support databases that do not have string-based queries, like MongoDB, fragments also allow keywords to be given:

from p in Post,
    where: fragment(title: ["$eq": ^some_value])
ilike(string, search)

Searches for search in string in a case insensitive fashion.

Translates to the underlying SQL ILIKE query.

from p in Post, where: ilike(p.body, "Chapter%")
left in right

Checks if the left-value is included in the right one.

from p in Post, where: p.id in [1, 2, 3]

The right side may either be a list, a literal list or even a column in the database with array type:

from p in Post, where: "elixir" in p.tags
is_nil(value)

Checks if the given value is nil.

from p in Post, where: is_nil(p.published_at)
like(string, search)

Searches for search in string.

Translates to the underlying SQL LIKE query.

from p in Post, where: like(p.body, "Chapter%")
max(value)

Calculates the maximum for the given entry.

from p in Payment, select: max(p.value)
min(value)

Calculates the minimum for the given entry.

from p in Payment, select: min(p.value)
not value

Unary not operation.

left or right

Binary or operation.

sum(value)

Calculates the sum for the given entry.

from p in Payment, select: sum(p.value)
type(interpolated_value, type)

Casts the given value to the given type.

Most of the times, Ecto is able to proper cast interpolated values due to its type checking mechanism. In some situations though, in particular when using fragments with fragment/1, you may want to tell Ecto you are expecting a particular type:

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

It is also possible to say the type must match the same of a column:

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