This protocol allows you to set query options in your Ecto schemas.

usage

Usage

Derive Flop.Schema in your Ecto schema and set the filterable and sortable fields.

defmodule Flop.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

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

iex> Flop.validate(%Flop{order_by: [:name]}, for: Flop.Pet)
{:ok,
 %Flop{
   filters: [],
   limit: nil,
   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: Flop.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

Default and maximum limits

To define a default or maximum limit, you can set the default_limit and max_limit option when deriving Flop.Schema. The maximum limit will be validated and the default limit applied by Flop.validate/1.

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

default-sort-order

Default sort order

To define a default sort order, you can set the default_order_by and default_order_directions options when deriving Flop.Schema. The default values are applied by Flop.validate/1. If no order directions are set, :asc is assumed 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

Restricting pagination types

By default, page/page_size, offset/limit and cursor-based pagination (first/after and last/before) are enabled. If you want to restrict the pagination type for a schema, you can do that by setting the pagination_types option.

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

See also Flop.option/0 and Flop.pagination_type/0. Setting the value to nil allows all pagination types.

compound-fields

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

Filtering

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

This would translate to:

WHERE family_name='margo' OR given_name ='margo'

Partial matches and splitting of the search term can be achieved with one of the ilike operators.

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

This would translate to:

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

filter-operator-rules

Filter operator rules

• :=~, :like, :like_and, :like_or, :ilike, :ilike_and, :ilike_or - The filter value is split at whitespace characters as usual. 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
  • 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. This will be added in a future version. These filter operators are ignored for compound fields at the moment.

sorting

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

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],
  join_fields: [pet_species: [binding: :pets, field: :species]]
}

In this case, :pet_species would be the alias of the field that you can refer to in the filter and order parameters. The :binding option refers to the named binding you set with the :as option in the join statement of your query. :field is the field name on that binding.

You can also use the short syntax:

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

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],
  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.