Flop.Schema is a protocol that allows you to customize and set query options in your Ecto schemas.

This module allows you to define which fields are filterable and sortable, set default and maximum limits, specify default sort orders, restrict pagination types, and more.

Usage

To utilize this protocol, derive Flop.Schema in your Ecto schema and define the filterable and sortable fields.

defmodule MyApp.Pet do
  use Ecto.Schema

  @derive {
    Flop.Schema,
    filterable: [:name, :species],
    sortable: [:name, :age]
  }

  schema "pets" do
    field :name, :string
    field :age, :integer
    field :species, :string
  end
end

See option/0 for an overview of all available options.

@derive Flop.Schema

When you derive Flop.Schema, all the functions required for the Flop.Schema protocol will be defined based on the options you set.

After that, you can pass the module as the :for option to Flop.validate/2.

iex> Flop.validate(%Flop{order_by: [:name]}, for: MyApp.Pet)
{:ok,
 %Flop{
   filters: [],
   limit: 50,
   offset: nil,
   order_by: [:name],
   order_directions: nil,
   page: nil,
   page_size: nil
 }}

iex> {:error, %Flop.Meta{} = meta} = Flop.validate(
...>   %Flop{order_by: [:species]}, for: MyApp.Pet
...> )
iex> meta.params
%{"order_by" => [:species], "filters" => []}
iex> meta.errors
[
  order_by: [
    {"has an invalid entry",
     [validation: :subset, enum: [:name, :age, :owner_name, :owner_age]]}
  ]
]

Default and maximum limits

Define a default or maximum limit by setting the default_limit and max_limit options while deriving Flop.Schema. Flop.validate/1 will apply the default limit and validate the maximum limit.

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  max_limit: 100,
  default_limit: 50
}

Default sort order

Specify a default sort order by setting the default_order_by and default_order_directions options when deriving Flop.Schema. The default values will be applied by Flop.validate/1. If no order directions are set, :asc is the default for all fields.

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  default_order: %{
    order_by: [:name, :age],
    order_directions: [:asc, :desc]
  }
}

Restricting pagination types

By default, all supported pagination types (Flop.pagination_type/0) are enabled. If you wish to restrict the pagination type for a schema, you can set the :pagination_types option.

@derive {
  Flop.Schema,
  filterable: [:name, :species],
  sortable: [:name, :age],
  pagination_types: [:first, :last]
}

Setting the value to nil (default) allows all pagination types.

See also Flop.option/0.

Alias fields

To sort by calculated values, you can use Ecto.Query.API.selected_as/2 in your query, define an alias field in your schema, and add the alias field to the list of sortable fields.

Schema:

@derive {
  Flop.Schema,
  filterable: [],
  sortable: [:pet_count],
  adapter_opts: [
    alias_fields: [:pet_count]
  ]
}

Query:

Owner
|> join(:left, [o], p in assoc(o, :pets), as: :pets)
|> group_by([o], o.id)
|> select(
  [o, pets: p],
  {o.id, p.id |> count() |> selected_as(:pet_count)}
)
|> Flop.validate_and_run(params, for: Owner)

Note that it is not possible to use field aliases in WHERE clauses, which means you cannot add alias fields to the list of filterable fields, and you cannot sort by an alias field if you are using cursor-based pagination.

Compound fields

Sometimes you might need to apply a search term to multiple fields at once, e.g. you might want to search in both the family name and given name field. You can do that with Flop by defining a compound field.

@derive {
  Flop.Schema,
  filterable: [:full_name],
  sortable: [:full_name],
  adapter_opts: [
    compound_fields: [full_name: [:family_name, :given_name]]
  ]
}

This allows you to use the field name :full_name as any other field in the filter and order parameters.

Filtering

params = %{
  filters: [%{
    field: :full_name,
    op: :like,
    value: "margo"
  }]
}

This would translate to:

WHERE family_name like '%margo%' OR given_name like '%margo%'

Partial matches of the search term can be achieved with one of the like operators.

params = %{
  filters: [%{
    field: :full_name,
    op: :ilike_and,
    value: ["margo", "martindale"]
  }]
}

or

params = %{
  filters: [%{
    field: :full_name,
    op: :ilike_and,
    value: "margo martindale"
  }]
}

This would translate to:

WHERE (family_name ilike '%margo%' OR given_name ilike '%margo%')
AND (family_name ilike '%martindale%' OR given_name ilike '%martindale%')

Filter operator rules

• :=~ :like :not_like :like_and :like_or :ilike :not_ilike :ilike_and :ilike_or
  If a string value is passed it will be split at whitespace characters and each segment will be checked separately. If a list of strings is passed the individual strings are not split. The filter matches for a value if it matches for any of the fields.
• :empty
  Matches if all fields of the compound field are nil.
• :not_empty
  Matches if any field of the compound field is not nil.
• :== :!= :<= :< :>= :> :in :not_in :contains :not_contains
  These filter operators are ignored for compound fields at the moment. This will be added in a future version.
  The filter value is normalized by splitting the string at whitespaces and joining it with a space. The values of all fields of the compound field are split by whitespace character and joined with a space, and the resulting values are joined with a space again.

Sorting

params = %{
  order_by: [:full_name],
  order_directions: [:desc]
}

This would translate to:

ORDER BY family_name DESC, given_name DESC

Note that compound fields cannot be used as pagination cursors.

Join fields

If you need to filter or order across tables, you can define join fields.

As an example, let's define these schemas:

schema "owners" do
  field :name, :string
  field :email, :string

  has_many :pets, Pet
end

schema "pets" do
  field :name, :string
  field :species, :string

  belongs_to :owner, Owner
end

And now we want to find all owners that have pets of the species "E. africanus". To do this, first we need to define a join field on the Owner schema.

@derive {
  Flop.Schema,
  filterable: [:pet_species],
  sortable: [:pet_species],
  adapter_opts: [
    join_fields: [
      pet_species: [
        binding: :pets,
        field: :species,
        ecto_type: :string
      ]
    ]
  ]
}

In this case, :pet_species would be the alias of the field that you can refer to in the filter and order parameters. The options are:

• :binding - The named binding you set with the :as option in the join statement of your query.
• :field - The field on that binding on which the filter should be applied.
• :ecto_type - The Ecto type of the field. This allows Flop to validate filter values, and also to treat empty arrays and empty maps as empty values depending on the type. See also Ecto type option section below.

In order to retrieve the pagination cursor value for a join field, Flop needs to know how to get the field value from the struct that is returned from the database. Flop.Schema.get_field/2 is used for that. By default, Flop assumes that the binding name matches the name of the field for the association in your Ecto schema (the one you set with has_one, has_many or belongs_to).

In the example above, Flop would try to access the field in the struct under the path [:pets, :species].

If you have joins across multiple tables, or if you can't give the binding the same name as the association field, you can specify the path explicitly.

@derive {
  Flop.Schema,
  filterable: [:pet_species],
  sortable: [:pet_species],
  adapter_opts: [
    join_fields: [
      pet_species: [
        binding: :pets,
        field: :species,
        path: [:pets, :species]
      ]
    ]
  ]
}

After setting up the join fields, you can write a query like this:

params = %{
  filters: [%{field: :pet_species, op: :==, value: "E. africanus"}]
}

Owner
|> join(:left, [o], p in assoc(o, :pets), as: :pets)
|> preload([pets: p], [pets: p])
|> Flop.validate_and_run!(params, for: Owner)

If your query returns data in a different format, you don't need to set the :path option. Instead, you can pass a custom cursor value function in the options. See Flop.Cursor.get_cursors/2 and Flop.option/0.

Note that Flop doesn't create the join clauses for you. The named bindings already have to be present in the query you pass to the Flop functions. You can use Flop.with_named_bindings/4 or Flop.named_bindings/3 to get the build the join clauses needed for a query dynamically and avoid adding unnecessary joins.

Filtering by calculated values with subqueries

You can join on a subquery with a named binding and add a join field as described above.

Schema:

@derive {
  Flop.Schema,
  filterable: [:pet_count],
  sortable: [:pet_count],
  adapter_opts: [
    join_fields: [
      pet_count: [
        binding: :pet_count,
        field: :count,
        ecto_type: :integer
      ]
    ]
  ]
}

Query:

params = %{filters: [%{field: :pet_count, op: :>, value: 2}]}

pet_count_query =
  Pet
  |> where([p], parent_as(:owner).id == p.owner_id)
  |> select([p], %{count: count(p)})

q =
  (o in Owner)
  |> from(as: :owner)
  |> join(:inner_lateral, [owner: o], p in subquery(pet_count_query),
    as: :pet_count
  )
  |> Flop.validate_and_run(params, for: Owner)

Custom fields

Custom fields allow for precise control over filter queries, making it possible to implement filter logic that the built-in filtering options cannot satisfy.

For example, you might need to handle dates and times in a particular way that takes into account different time zones, or perform database-specific queries using fragments.

Custom field filters are referenced by a tuple {mod :: module, function :: atom, opts :: keyword}. The referenced function receives three arguments: the Ecto query, the Flop filter, and an options keyword list.

If runtime options are necessary (like the timezone of the request or the user ID of the current user), use the extra_opts option when calling Flop functions.

Note that as of now, custom fields only support filtering, not sorting.

Schema:

@derive {
  Flop.Schema,
  filterable: [:inserted_at_date],
  adapter_opts: [
    custom_fields: [
      inserted_at_date: [
        filter: {CustomFilters, :date_filter, [source: :inserted_at]},
        ecto_type: :date,
        operators: [:<=, :>=]
      ]
    ]
  ]
}

If you pass the :ecto_type option like above, the filter value will be automatically cast.

Filter module:

defmodule CustomFilters do
  import Ecto.Query

  def date_filter(query, %Flop.Filter{value: value, op: op}, opts) do
    source = Keyword.fetch!(opts, :source)
    timezone = Keyword.fetch!(opts, :timezone)

    expr = dynamic(
      [r],
      fragment("((? AT TIME ZONE 'utc') AT TIME ZONE ?)::date",
      field(r, ^source), ^timezone)
    )

    conditions =
      case op do
        :>= -> dynamic([r], ^expr >= ^value)
        :<= -> dynamic([r], ^expr <= ^value)
      end

    where(query, ^conditions)
  end
end

Query:

Flop.validate_and_run(
  MyApp.Pet,
  params,
  for: MyApp.Pet,
  extra_opts: [timezone: timezone]
)

If your custom filter requires certain named bindings, you can use the :bindings option to specify them. Then, using Flop.with_named_bindings/4, these bindings can be conditionally added to your query based on filter conditions.

Ecto type option

Flop automatically retrieves the field type from the schema module for regular schema fields, enabling it to correctly cast filter values. Compound fields are always treated as string fields.

For join and custom fields, Flop cannot automatically determine the Ecto type. Therefore, you need to specify the ecto_type option. This helps Flop cast filter values for join and custom fields properly. If this option is not set, Flop will accept any filter value, potentially leading to an Ecto.Query.CastError if an invalid filter value is used. Additionally, without this option, Flop cannot identify empty lists and maps as empty values for array and map fields.

@derive {
  Flop.Schema,
  filterable: [:full_text, :pet_species],
  sortable: [:id],
  adapter_opts: [
    join_fields: [
      pet_species: [
        binding: :pets,
        field: :species,
        ecto_type: :string
      ]
    ],
    custom_fields: [
      full_text: [
        filter: {__MODULE__, :full_text_filter, []},
        ecto_type: :string
      ]
    ]
  ]
}

You can specify any Ecto type with the ecto_type option. Here are some examples:

• A simple string: ecto_type: :string
• An integer: ecto_type: :integer
• An array of strings: ecto_type: {:array, :string}
• A custom Ecto type: ecto_type: MyCustomType

For parameterized types, use the following syntax:

• ecto_type: {:parameterized, Ecto.Enum, Ecto.Enum.init(values: [:one, :two])}

If you're working with Ecto.Enum types, you can use a more convenient syntax:

• ecto_type: {:ecto_enum, [:one, :two]}

Furthermore, you can reference a type from another schema:

• ecto_type: {:from_schema, MyApp.Pet, :mood}

Note that Flop.Phoenix encodes all filters in query string using Plug.Conn.Query. It is expected that filter values can be converted to strings with to_string/1. If you are using an Ecto custom type that casts as a struct, you will need to implement the String.Chars protocol for that struct.