Dynamic filtering of Ecto queries is useful for creating list/index functions, and ultimately list/index endpoints, that accept a map of filters to apply to the query. Such a map can be based on HTTP query parameters, naturally.

Several filtering types are so common that they have been implemented using standard filter macro's. This way, you only have to define which fields are filterable in what way.

Documentation for such filters can be autogenerated using generate_filter_docs/2.

example-without-standard-filters

Example without standard filters

import Ecto.Query
require Ecto.Query

def list_without_standard_filters(filters \\ %{}) do
  base_query = from(user in "users", as: :user)

  filters
  |> Enum.reduce(base_query, fn
    {:order_by, {field, direction}}, query ->
      order_by(query, [user: user], [{^direction, field(user, ^field)}])

    {filter, value}, query when filter in [:id, :name, :residence, :address] ->
      where(query, [user: user], field(user, ^filter) == ^value)

    _, query ->
      query
  end)
end

# filtering is optional
iex> list_without_standard_filters()
#Ecto.Query<from u0 in "users", as: :user>

# multiple equal_to matches can be combined
iex> list_without_standard_filters(%{residence: "New York", address: "Main Street"})
#Ecto.Query<from u0 in "users", as: :user, where: u0.address == ^"Main Street", where: u0.residence == ^"New York">

# equal_to matches and sorting can be combined
iex> list_without_standard_filters(%{residence: "New York", order_by: {:name, :desc}})
#Ecto.Query<from u0 in "users", as: :user, where: u0.residence == ^"New York", order_by: [desc: u0.name]>

# other fields are ignored / passed through
iex> list_without_standard_filters(%{number_of_arms: 3})
#Ecto.Query<from u0 in "users", as: :user>

example-with-standard-filters-and-autogenerated-docs

Example with standard filters and autogenerated docs

Standard filters can be applied using the standard_filters/6 macro. It supports various filtering styles: equal_to matches, set membership, smaller/greater than comparisons, ordering and pagination. These filters must be configured at compile time. Standard filters can be combined with non-standard custom filters. Documentation can be autogenerated.

@filter_definitions [
  atom_keys: true,
  string_keys: true,
  limit: true,
  offset: true,
  order_by: true,
  order_by_aliases: [
    role_name: {:role, :name},
    username_last_letter: &__MODULE__.order_by_username_last_letter/2
  ],
  equal_to: [:id, :username, :address, :balance, role_name: {:role, :name}],
  equal_to_any: [:address],
  string_starts_with: [username_prefix: {:user, :username}],
  string_contains: [username_search: :username],
  list_contains: [:roles],
  list_contains_any: [:roles],
  list_contains_all: [all_roles: :roles],
  smaller_than: [
    inserted_before: :inserted_at,
    balance_lt: :balance,
    role_inserted_before: {:role, :inserted_at}
  ],
  greater_than_or_equal_to: [
    inserted_at_or_after: :inserted_at,
    balance_gte: :balance
  ],
  filter_by: [
    group_name_alternative: &__MODULE__.by_group_name/2
  ]
]

@doc """
Custom filter function
"""
def by_group_name(query, group_name) do
  where(query, [user: user], user.group_name == ^group_name)
end

@doc """
Custom order_by handler
"""
def order_by_username_last_letter(query, direction) do
  order_by(query, [user: user], [{^direction, fragment("right(?, 1)", user.username)}])
end

@doc """
Function to resolve named bindings by dynamically joining them into the query.
"""
def resolve_binding(query, named_binding) do
  if has_named_binding?(query, named_binding) do
    query
  else
    case named_binding do
      :role -> join(query, :left, [user: user], role in "roles", as: :role)
      _ -> query
    end
  end
end

@doc """
My awesome list function. You can filter it, you know! And we guarantee the docs are up-to-date!

#{generate_filter_docs(@filter_definitions, equal_to: [:group_name])}
"""
def list_with_standard_filters(filters \\ %{}) do
  from(user in "users", as: :user)
  |> standard_filters filters, :user, @filter_definitions, &resolve_binding/2 do
    # Add custom filters first and fall back to standard filters
    {:group_name, value}, query -> by_group_name(query, value)
  end)
end

# filtering is optional
iex> list_with_standard_filters()
#Ecto.Query<from u0 in "users", as: :user>

# let's do some filtering
iex> list_with_standard_filters(%{username: "Peter", balance_lt: 50.00, address: "sesame street"})
#Ecto.Query<from u0 in "users", as: :user, where: u0.address == ^"sesame street", where: u0.balance < ^50.0, where: u0.username == ^"Peter">

# associations can be dynamically joined into the query, only when necessary
iex> list_with_standard_filters(%{role_name: "admin"})
#Ecto.Query<from u0 in "users", as: :user, left_join: r1 in "roles", as: :role, on: true, where: r1.name == ^"admin">

# limit, offset, and order_by are supported
iex> list_with_standard_filters(%{"limit" => 10, offset: 1, order_by: [desc: :address]})
#Ecto.Query<from u0 in "users", as: :user, order_by: [desc: u0.address], limit: ^10, offset: ^1>

# order_by can use association fields as well, which are dynamically joined in that case
iex> list_with_standard_filters(%{order_by: [asc: {:role, :name}]})
#Ecto.Query<from u0 in "users", as: :user, left_join: r1 in "roles", as: :role, on: true, order_by: [asc: r1.name]>

# order_by can use aliases defined in `order_by_aliases`, without breaking dynamic joining
iex> list_with_standard_filters(%{order_by: [asc: :role_name]})
#Ecto.Query<from u0 in "users", as: :user, left_join: r1 in "roles", as: :role, on: true, order_by: [asc: r1.name]>

# order_by can use function aliases
iex> list_with_standard_filters(%{order_by: [desc: :username_last_letter]})
#Ecto.Query<from u0 in "users", as: :user, order_by: [desc: fragment("right(?, 1)", u0.username)]>

# complex custom filters can be combined with the standard filters
iex> list_with_standard_filters(%{group_name: "admins", balance_gte: 50.00})
#Ecto.Query<from u0 in "users", as: :user, where: u0.balance >= ^50.0, where: u0.group_name == ^"admins">

# a custom filter function may be passed into :filter_by as well
iex> list_with_standard_filters(%{group_name_alternative: "admins"})
#Ecto.Query<from u0 in "users", as: :user, where: u0.group_name == ^"admins">

# unsupported filters raise, but nonexistent order_by fields do not (although Ecto will raise, naturally)
iex> list_with_standard_filters(%{number_of_arms: 3})
** (RuntimeError) list filter {:number_of_arms, 3} not recognized
iex> list_with_standard_filters(%{order_by: [:number_of_arms]})
#Ecto.Query<from u0 in "users", as: :user, order_by: [asc: u0.number_of_arms]>

# filtering on lists of values, string prefixes and string-contains filters
iex> list_with_standard_filters(%{address: ["sesame street"], username_prefix: "foo", username_search: "bar"})
#Ecto.Query<from u0 in "users", as: :user, where: u0.address in ^["sesame street"], where: ilike(u0.username, ^"foo%"), where: ilike(u0.username, ^"%bar%")>

# filtering on array-type fields
iex> list_with_standard_filters(%{roles: "admin"})
#Ecto.Query<from u0 in "users", as: :user, where: fragment("? && ?", u0.roles, ^["admin"])>
iex> list_with_standard_filters(%{roles: ["admin", "superadmin"], all_roles: ["creator", "user"]})
#Ecto.Query<from u0 in "users", as: :user, where: fragment("? @> ?", u0.roles, ^["creator", "user"]), where: fragment("? && ?", u0.roles, ^["admin", "superadmin"])>

# you can order by multiple fields and specify bindings
iex> list_with_standard_filters(%{"balance" => 12, "order_by" => [asc: {:user, :username}, desc: :role]})
#Ecto.Query<from u0 in "users", as: :user, where: u0.balance == ^12, order_by: [asc: u0.username], order_by: [desc: u0.role]>

Note that the aim is not to emulate GraphQL in a REST API. It is not possible for the API client to specify which fields the API should return or how deep the nesting should be: it is still necessary to develop different REST resources for differently-shaped responses (for example, /api/users or /api/users_with_groups etc). In a REST API, simple filtering and sorting functionality can be supported, however, without going the full GraphQL route. We will not discuss the pro's and cons of GraphQL versus REST here, but we maintain that GraphQL is not a drop-in replacement for REST API's in every situation and there is still a place for (flexible) REST API's, for example when caching on anything other than the client itself is desired or when development simplicity trumps complete flexibility and the number of different clients is limited.

generating-documentation

Generating documentation

The call to generate_filter_docs/2 for the filter definitions as defined above will generate the following (rendered) docs:

filter-key-types

Filter key types

Filter keys may be both atoms and strings, e.g. %{username: "Dave123", "first_name" => "Dave"}

equal-to-filters

Equal-to filters

The field's value must be equal to the filter value. The equivalent Ecto code is

where(query, [binding: bd], bd.field == ^filter_value)

The following filter names are supported:

• address
• balance
• group_name
• id
• role_name (actual field is role.name)
• username

equal-to-any-filters

Equal-to-any filters

The field's value must be equal to any of the filter values. The equivalent Ecto code is

where(query, [binding: bd], bd.field in ^filter_value)

The following filter names are supported:

• address

smaller-than-filters

Smaller-than filters

The field's value must be smaller than the filter's value. The equivalent Ecto code is

where(query, [binding: bd], bd.field < ^filter_value)

The following filter names are supported:

Filter name	Must be smaller than
balance_lt	balance
inserted_before	inserted_at
role_inserted_before	role.inserted_at

greater-than-or-equal-to-filters

Greater-than-or-equal-to filters

The field's value must be greater than or equal to the filter's value. The equivalent Ecto code is

where(query, [binding: bd], bd.field >= ^filter_value)

The following filter names are supported:

Filter name	Must be greater than or equal to
balance_gte	balance
inserted_at_or_after	inserted_at

string-starts-with-filters

String-starts-with filters

The string-type field's value must start with the filter's value. The equivalent Ecto code is

where(query, [binding: bd], ilike(bd.field, ^(val <> "%")))

The following filter names are supported:

• username_prefix (actual field is user.username)

string-contains-filters

String-contains filters

The string-type field's value must contain the filter's value. The equivalent Ecto code is

where(query, [binding: bd], ilike(bd.field, ^("%" <> val <> "%")))

The following filter names are supported:

• username_search (actual field is username)

list-contains-filters

List-contains filters

The array-type field's value must contain the filter's value (set membership). The equivalent Ecto code is

where(query, [binding: bd], fragment("? && ?", bd.field, ^[val]))

The following filter names are supported:

• roles

list-contains-any-filters

List-contains-any filters

The array-type field's value must contain any of the filter's values (set intersection). The equivalent Ecto code is

where(query, [binding: bd], fragment("? && ?", bd.field, ^val))

The following filter names are supported:

• roles

list-contains-all-filters

List-contains-all filters

The array-type field's value must contain all of the filter's values (subset). The equivalent Ecto code is

where(query, [binding: bd], fragment("? @> ?", bd.field, ^val))

The following filter names are supported:

• all_roles (actual field is roles)

order-by-sorting

Order-by sorting

Order-by filters do not actually filter the result set, but sort it according to the filter's value(s). The supported directions can be found in the docs of Ecto.Query.order_by/3.

Order-by filters take a list argument, that can consist of the following elements:

• field will sort on the specified field of the default binding in ascending order
• {:direction, :field} will sort on the specified field of the default binding in the specified direction
• {:direction, {:binding, :field}} will sort on the specified field of the specified binding in the specified direction.

Note that the value of order_by filters must consist of atoms, even with string_keys enabled.

All fields present in the query on any named binding are supported. Additionally, aliases for fields in non-default bindings can be defined in order_by_aliases. The alias can then be used in order_by filters. The following aliases are supported:

• role_name (actual field is role.name)
• username_last_letter (opague)

limit-filter

Limit filter

The limit filter sets a maximum for the number of rows in the result set and may be used for pagination.

offset-filter

Offset filter

The offset filter skips a number of rows in the result set and may be used for pagination.

filter-by-function-filters

Filter-by-function filters

The filter applies a function to the query.

The following filter names are supported:

• group_name_alternative (opague)