View Source Flop.Schema protocol (Flop v0.19.0)
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: 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: 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.
alias-fields
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],
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
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 of the search term can be achieved with one of the ilike 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 ='%margo%')
AND (family_name ilike '%martindale%' OR given_name ='%martindale%')
filter-operator-rules
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 for. 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 arenil
.:not_empty
- Matches if any field of the compound field is notnil
.:==
,:!=
,:<=
,:<
,:>=
,:>
,: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 set the ecto_type
option, which allows Flop to determine which
filter operators can be used on the field during the validation. This is also
important if the field is a map or array field, so that Flop can check for
empty arrays and empty maps when a empty
or not_empty
filter is used.
@derive {
Flop.Schema,
filterable: [:pet_species],
sortable: [:pet_species],
join_fields: [
pet_species: [
binding: :pets,
field: :species,
ecto_type: :string
]
]
}
There is also a short syntax which you can use if you only want to specify the binding and the field:
@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. 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
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],
join_fields: [pet_count: [{:pet_count, :count}]}
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
If you need more control over the queries produced by the filters, you can
define custom fields that reference a function which implements the filter
logic. Custom field filters are referenced by
{mod :: module, function :: atom, opts :: keyword}
. The function will
receive the Ecto query, the flop filter, and the option keyword list.
If you need to pass in options at runtime (e.g. the timezone of the request,
the user ID of the current user etc.), you can do so by passing in the
extra_opts
option to the flop functions. Currently, custom fields only
support filtering and can not be used for sorting.
Schema:
@derive {
Flop.Schema,
filterable: [:inserted_at_date],
custom_fields: [
inserted_at_date: [
filter: {CustomFilters, :date_filter, [source: :inserted_at]},
ecto_type: :date
]
]
}
Filter module:
defmodule CustomFilters do
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)
case Ecto.Type.cast(:date, value) do
{:ok, date} ->
conditions =
case op do
:>= -> dynamic([r], ^expr >= ^date)
:<= -> dynamic([r], ^expr <= ^date)
end
where(query, ^conditions)
:error ->
query
end
end
end
Query:
Flop.validate_and_run(Flop.Pet, params, for: Flop.Pet, extra_opts: [timezone: timezone])
Link to this section Summary
Functions
Returns the default limit of a schema.
Returns the default order of a schema.
Returns the field type in a schema.
Returns the filterable fields of a schema.
Gets the field value from a struct.
Returns the maximum limit of a schema.
Returns the allowed pagination types of a schema.
Returns the sortable fields of a schema.
Link to this section Types
@type t() :: term()
Link to this section Functions
@spec default_limit(any()) :: pos_integer() | nil
Returns the default limit of a schema.
iex> Flop.Schema.default_limit(%Flop.Fruit{})
60
@spec default_order(any()) :: %{order_by: [atom()] | nil, order_directions: [Flop.order_direction()] | nil} | nil
Returns the default order of a schema.
iex> Flop.Schema.default_order(%Flop.Fruit{})
%{order_by: [:name], order_directions: [:asc]}
@spec field_type(any(), atom()) :: {:normal, atom()} | {:compound, [atom()]} | {:join, map()} | {:alias, atom()} | {:custom, map()}
Returns the field type in a schema.
{:normal, atom}
- An ordinary field on the schema. The second tuple element is the field name.{:compound, [atom]}
- A combination of fields defined with thecompound_fields
option. The list of atoms refers to the list of fields that are included.{:join, map}
- A field from a named binding as defined with thejoin_fields
option. The map has keys for the:binding
,:field
and:path
.{:custom, keyword}
- A filter field that uses a custom filter function.
examples
Examples
iex> field_type(%Flop.Pet{}, :age)
{:normal, :age}
iex> field_type(%Flop.Pet{}, :full_name)
{:compound, [:family_name, :given_name]}
iex> field_type(%Flop.Pet{}, :owner_name)
{
:join,
%{
binding: :owner,
field: :name,
path: [:owner, :name],
ecto_type: :string
}
}
iex> field_type(%Flop.Pet{}, :reverse_name)
{
:custom,
%{
filter: {Flop.Pet, :reverse_name_filter, []},
ecto_type: :string
}
}
Returns the filterable fields of a schema.
iex> Flop.Schema.filterable(%Flop.Pet{})
[
:age,
:full_name,
:name,
:owner_age,
:owner_name,
:owner_tags,
:pet_and_owner_name,
:species,
:tags,
:custom,
:reverse_name
]
Gets the field value from a struct.
Resolves join fields and compound fields according to the config.
# join_fields: [owner_name: {:owner, :name}]
iex> pet = %Flop.Pet{name: "George", owner: %Flop.Owner{name: "Carl"}}
iex> Flop.Schema.get_field(pet, :name)
"George"
iex> Flop.Schema.get_field(pet, :owner_name)
"Carl"
# compound_fields: [full_name: [:family_name, :given_name]]
iex> pet = %Flop.Pet{given_name: "George", family_name: "Gooney"}
iex> Flop.Schema.get_field(pet, :full_name)
"Gooney George"
For join fields, this function relies on the binding name in the schema config matching the field name for the association in the struct.
@spec max_limit(any()) :: pos_integer() | nil
Returns the maximum limit of a schema.
iex> Flop.Schema.max_limit(%Flop.Pet{})
1000
@spec pagination_types(any()) :: [Flop.pagination_type()] | nil
Returns the allowed pagination types of a schema.
iex> Flop.Schema.pagination_types(%Flop.Fruit{})
[:first, :last, :offset]
Returns the sortable fields of a schema.
iex> Flop.Schema.sortable(%Flop.Pet{})
[:name, :age, :owner_name, :owner_age]