View Source PhoenixApiToolkit.Ecto.DynamicFilters (Phoenix API Toolkit v3.1.2)

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 nameMust be smaller than
balance_ltbalance
inserted_beforeinserted_at
role_inserted_beforerole.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 nameMust be greater than or equal to
balance_gtebalance
inserted_at_or_afterinserted_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)

Link to this section Summary

Types

Extra filters supported by a function, for which documentation should be generated by generate_filter_docs/2. A keyword list of filter types and the fields for which documentation should be generated.

Format of a filter that can be applied to a query to narrow it down

Definition used to generate a filter for standard_filters/6.

Filter definitions supported by standard_filters/6. A keyword list of filter types and the filter definitions for which they should be generated.

Functions

Generate a markdown docstring from filter definitions, as passed to standard_filters/6, as defined by filter_definitions/0. By specifying extras, documentation can be generated for any custom filters supported by your function as well.

Applies standard filters to the query. Standard filters include filters for equal_to matches, set membership, smaller/greater than comparisons, ordering and pagination.

Link to this section Types

Link to this type

extra_filter_definitions()

View Source
@type extra_filter_definitions() :: [
  order_by_aliases: [filter_definition()],
  equal_to: [filter_definition()],
  equal_to_any: [filter_definition()],
  smaller_than: [filter_definition()],
  smaller_than_or_equal_to: [filter_definition()],
  greater_than: [filter_definition()],
  greater_than_or_equal_to: [filter_definition()],
  string_starts_with: [filter_definition()],
  string_contains: [filter_definition()],
  list_contains: [filter_definition()],
  list_contains_any: [filter_definition()],
  list_contains_all: [filter_definition()]
]

Extra filters supported by a function, for which documentation should be generated by generate_filter_docs/2. A keyword list of filter types and the fields for which documentation should be generated.

@type filter() :: {atom() | String.t(), any()}

Format of a filter that can be applied to a query to narrow it down

@type filter_definition() :: atom() | {atom(), atom()} | {atom(), {atom(), atom()}}

Definition used to generate a filter for standard_filters/6.

May take the following forms:

  • atom filter name and name of field of default binding
  • {filter_name, actual_field} if the filter name is different from the name of the field of the default binding
  • {filter_name, {binding, actual_field}} if the field is a field of another named binding
@type filter_definitions() :: [
  atom_keys: boolean(),
  string_keys: boolean(),
  limit: boolean(),
  offset: boolean(),
  order_by: boolean(),
  order_by_aliases: [
    filter_definition() | (Ecto.Query.t(), atom() -> Ecto.Query.t())
  ],
  equal_to: [filter_definition()],
  equal_to_any: [filter_definition()],
  smaller_than: [filter_definition()],
  smaller_than_or_equal_to: [filter_definition()],
  greater_than: [filter_definition()],
  greater_than_or_equal_to: [filter_definition()],
  string_starts_with: [filter_definition()],
  string_contains: [filter_definition()],
  list_contains: [filter_definition()],
  list_contains_any: [filter_definition()],
  list_contains_all: [filter_definition()]
]

Filter definitions supported by standard_filters/6. A keyword list of filter types and the filter definitions for which they should be generated.

Link to this section Functions

Link to this function

generate_filter_docs(filters, extras \\ [])

View Source
@spec generate_filter_docs(filter_definitions(), extra_filter_definitions()) ::
  binary()

Generate a markdown docstring from filter definitions, as passed to standard_filters/6, as defined by filter_definitions/0. By specifying extras, documentation can be generated for any custom filters supported by your function as well.

See the module docs Elixir.PhoenixApiToolkit.Ecto.DynamicFilters for details and examples.

Link to this macro

standard_filters(query, filters, default_binding, filter_definitions, resolve_binding, overrides \\ nil)

View Source (macro)
@spec standard_filters(
  Ecto.Query.t(),
  filter(),
  atom(),
  filter_definitions(),
  (Ecto.Query.t(), atom() -> Ecto.Query.t()),
  any()
) :: any()

Applies standard filters to the query. Standard filters include filters for equal_to matches, set membership, smaller/greater than comparisons, ordering and pagination.

See the module docs Elixir.PhoenixApiToolkit.Ecto.DynamicFilters for details and examples.

Mandatory parameters:

  • query: the Ecto query that is narrowed down
  • filter: the current filter that is being applied to query
  • default_binding: the named binding of the Ecto model that generic queries are applied to, unless specified otherwise
  • filter_definitions: keyword list of filter types and the filter definitions for which they should be generated

Optional parameters:

  • resolve_binding: a function that can be passed in to dynamically join the query to resolve named bindings requested in filters

The options supported by the filter_definitions parameter are:

  • atom_keys: supports filter keys as atoms, e.g. %{username: "Dave"}
  • string_keys: supports filter keys as strings, e.g. %{"username" => "Dave"}. Note that order_by VALUES must always be atoms: %{"order_by" => :username} will work but %{order_by: "username"} will not.
  • limit: enables limit filter
  • offset: enables offset filter
  • order_by: enables order_by filter
  • order_by_aliases: set an alias for a non-default-binding-field, e.g. {:role_name, {:role, :name}} which enables order_by: [desc: :role_name] OR provide an ordering function that takes the query and direction as arguments
  • equal_to: field must be equal to filter.
  • equal_to_any: field must be equal to any value of filter, e.g. user.id in [1, 2, 3]. Filter names can be the same as equal_to filters.
  • smaller_than: field must be smaller than filter value, e.g. user.score < value
  • smaller_than_or_equal_to: field must be smaller than or equal to filter value, e.g. user.score <= value
  • greater_than: field must be greater than filter value, e.g. user.score > value
  • greater_than_or_equal_to: field must be greater than or equal to filter value, e.g. user.score >= value
  • string_starts_with: string field must start with case-insensitive string prefix, e.g. user.name starts with "dav"
  • string_contains: string field must contain case-insensitive string, e.g. user.name contains "av"
  • list_contains: array field must contain filter value, e.g. "admin" in user.roles (equivalent to set membership)
  • list_contains_any: array field must contain any filter value, e.g. user.roles contains any of ["admin", "creator"] (equivalent to set intersection). Filter names can be the same as list_contains filters.
  • list_contains_all: array field must contain all filter values, e.g. user.roles contains all of ["admin", "creator"] (equivalent to subset). Filter names can be the same as list_contains filters.
  • filter_by: filter with a custom function