Binary != operation.

Binary < operation.

Binary <= operation.

Binary == operation.

Binary > operation.

Binary >= operation.

Binary and operation.

Calculates the average for the given entry.

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

Counts the given entry.

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

Counts the distinct values in given entry.

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

Adds a given interval to a date.

See datetime_add/3 for more information.

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.

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.

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

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

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

Checks if the given value is nil.

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

Searches for search in string.

Translates to the underlying SQL LIKE query.

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

Calculates the maximum for the given entry.

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

Calculates the minimum for the given entry.

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

Unary not operation.

Binary or operation.

Calculates the sum for the given entry.

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

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)