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.

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, :mood, :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]
  }
}

Tiebreaker

If your order does not identify each row uniquely, tied rows come back in an arbitrary order, and cursor pagination over that order skips rows. To prevent that, Flop appends the schema's primary key to every order. Set the :tiebreaker option to change the direction, to name other fields, or to turn it off.

@derive {
  Flop.Schema,
  filterable: [:name],
  sortable: [:name],
  tiebreaker: {:primary_key, :desc}
}

See Flop.tiebreaker/0 for the accepted values. You can also set the option on a backend module, in the application environment, or per query function call. The keyword list form can only be set on a schema or in a query function call.

A tiebreaker field is only appended if the order parameters do not already contain it. If the tiebreaker option is set to :primary_key (the default) or {:primary_key, direction}, and the schema has no primary key, no tiebreaker will be added.

The tiebreaker is only applied when Flop orders the query. If you set ordering: false and no default order, the tiebreaker is not added.

The tiebreaker is not part of the Flop.t/0 struct, so it does not appear in the query parameters. Flop.ordering/2 returns the full order that is applied.

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 filtering and ordering, making it possible to implement logic that the built-in 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.

Both callbacks are referenced by a tuple {mod :: module, function :: atom, opts :: keyword}.

• field_dynamic returns the field itself as an Ecto.Query.dynamic_expr. It only receives an options keyword list, and Flop applies the ordering clauses and the filter operators on the expression it returns. This option is required to make a custom field sortable. It is also the simpler way to make a custom field filterable, as you only need to define a dynamic that returns a field value, and Flop can apply all operators on it.
• filter takes the Ecto query, the Flop filter and an options keyword list, and returns the updated query. With this option, you can build more complex filter expressions, but you have to handle all operators yourself.

If both functions are configured, filter is used for filtering and field_dynamic for sorting.

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.

Schema:

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

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

Custom field module:

defmodule CustomFields 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

  def date_field(opts) do
    source = Keyword.fetch!(opts, :source)
    timezone = Keyword.fetch!(opts, :timezone)

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

Query:

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

If either callback 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.

Cursor pagination with custom fields

A custom field with a field_dynamic can be used for cursor pagination. As with join fields, Flop reads the cursor value from the returned struct or map, and the :path option says where to find it. It defaults to the field name.

Add a virtual field to your schema and select the same dynamic that field_dynamic returns:

schema "pets" do
  field :inserted_at, :utc_datetime
  field :inserted_at_date, :date, virtual: true
end

dynamic =
  CustomFields.date_field(source: :inserted_at, timezone: timezone)

MyApp.Pet
|> select_merge(^%{inserted_at_date: dynamic})
|> Flop.validate_and_run(params,
  for: MyApp.Pet,
  extra_opts: [timezone: timezone]
)

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.

@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: Ecto.ParameterizedType.init(Ecto.Enum, 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}

Naming a module here makes it a compile-time dependency of your schema, so changing MyApp.Pet recompiles every schema that references it. This is not specific to Flop: Ecto.Schema reads the @derive attribute at compile time, which turns any module named in it into a compile-time dependency. Setting the type directly (ecto_type: :string) avoids it. If you want the reference and not the dependency, build the module name without an alias:

@pet Module.concat(["MyApp", "Pet"])

@derive {
  Flop.Schema,
  filterable: [:pet_mood],
  sortable: [],
  adapter_opts: [
    join_fields: [
      pet_mood: [
        binding: :pets,
        field: :mood,
        ecto_type: {:from_schema, @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.