Flop - Filtering, sorting and pagination for Ecto

Your Libraries & OS Mentoring Libraries
, , , ,
7

If you run a cursor-paginated query, Flop will make the query with the given limit + 1 (first, last, or default_limit). The *run functions will remove the extra row from the result set. The returned Flop.Meta struct has has_next_page? or has_previous_page? fields, where the value of one of the fields depends on the existence of the extra row, and the other one depends on the presence of a after or before parameter.

2 Likes
8

Thanks. Is it possible to do a range/between filtering on the same field with this library? For example records between two dates.

9

Yes, filters are passed as a list, you can combine as many as you want, including ones on the same field. All filters are combined with a logical AND. One thing that isn’t possible at the moment is combining multiple filters with a logical OR. (If you want to filter a single string field, there are ilike_or and like_or operators`, though.)

1 Like
10

Have you given any thought to including this approach for performance in a future release?

Otherwise, looks great!

2 Likes
11

That is already implemented! You can choose between offset-based and cursor-based pagination. See also:

Flop.Phoenix has two different UI components for offset-based and cursor-based pagination.

4 Likes
12

My company has early-adopted Flop & Flop.Phoenix in May 2021 and we have been using it for admin dashboards in multiple apps since, in production environments. We especially love the sweetspot position Flop.Phoenix takes between “being a logic-less table component” and a full-blown UI generator, giving us enough out-of-the-box functionality while allowing for easy customization. It’s been working like a charm and @woylie has been very generously devoting time to the issues we’ve raised and ideas we had.

Sounds a bit like an advertisement ^^ :smile: What I’m trying to say is: This pair of libraries deserves a lot more attention.

Thank you @woylie for all the work :purple_heart:

6 Likes
13

Flop 0.19 comes with custom fields, a new helper for dynamically adding bindings, and a set of filter manipulation functions.

Added

  • Support for custom fields. These fields allow you to run custom filter
    functions for anything that cannot be expressed with Flop filters.
  • Added Flop.with_named_bindings/4 for dynamically adding bindings needed for
    a Flop query.
  • Added fetch, get, get_all, delete, delete_first, drop, new,
    take, pop, pop_first, put and put_new functions to Flop.Filter.
  • Added Flop.Meta.with_errors/3.
  • Added ecto_type option to join fields.
  • Added not_like and not_ilike filter operators.
  • Added a cheatsheet for schema configuration.

Changed

  • Renamed Flop.bindings/3 to Flop.named_bindings/3.
  • Flop.Filter.allowed_operators/2 now tries to determine the Ecto type by
    reading the Flop field type from the schema module. This function is used
    during parameter validation, which means the validation step will be a bit
    stricter now. For join and custom fields, the Ecto type is determined via the
    new ecto_type option. If the option is not set, the function returns all
    operators as before. For compound fields, only the supported operators are
    returned.
  • Added opts field to Flop.Meta struct.
7 Likes
14

Flop 0.20.0

Added

  • Added Flop.unnest_filters/3 as a reverse operation of Flop.nest_filters/3
    after retrieving data from the database.
  • Added Flop.Filter.fetch_value/2, Flop.Filter.get_value/2,
    Flop.Filter.put_value/4, Flop.Filter.put_new_value/4,
    Flop.Filter.pop_value/3 and Flop.Filter.pop_first_value/3.

Changed

  • Several of the functions for manipulating lists of filters in the
    Flop.Filter module now accept lists of maps with atom keys, lists of maps
    with string keys, and indexed maps as produced by Phoenix HTML forms as
    argument.
  • The empty and not_empty operators now treat empty maps as empty values on
    map fields and empty arrays as empty values on array fields.
  • % and _ characters in filter values for the like, ilike and =~
    operators are now escaped.

Fixed

  • Fixed an issue that caused filter conditions for like_and, like_or,
    ilike_and and ilike_or to be incorrectly combined when applied to compound
    fields.
4 Likes
15

Very cool library!

Wondering how to make the following possible - we want to be able to filter by any field as developers. But we want to also restrict what the user can filter on. What is the best way to implement it?

def list_workflows(%{} = params, opts \\ []) do
  opts = Keyword.put_new(:for, Workflow)

  with {:ok, %Flop{} = flop} <- Flop.validate(params, opts),
       {:ok, %Flop{} = flop} <- custom_validation(flop, params, opts) do
    Flop.run(Workflow, flop, for: Workflow)
  end
end

Would custom_validation() be a good place for it? Then I can pass the list of allowed fields in opts.

1 Like
16

Yes, that would be one way to do it, with the minor caveat that Flop would still validate all the filters in your example, even the ones that will be removed by the custom_validation function.

Since Flop 0.20.0, the functions in Flop.Filter are quite permissive regarding the input format, so you might be able to use Flop.Filter.drop/2 on the filter parameters before passing them to the validate function. The functions in the Filter module accept the list of filters as an argument, and not the whole parameter map, though, so depending on your application, you need to be careful when you get the filter list from the parameters, since the parameters can be a map with string keys or a map with atom keys.

Maybe it would be useful if we allow overriding the filterable fields in the options in a future version.

17

Thanks. I also saw another one Flop.Filter.take/2 so I can just pass allowed fields to it.

18
2 Likes
19

A post was split to a new topic: Flop ordering

20

[0.20.2] - 2023-06-09

Changed

  • Added nutrition facts about use Flop and @derive Flop.Schema.
  • The minimum Elixir version is now 1.11.

Fixed

  • Fixed a deprecation warning about Logger.warn/1.
  • Fixed a deprecation warning about passing an MFA to :with in
    cast_assoc/cast_embed introduced in Ecto 3.10.2.
4 Likes
21

[0.20.3] - 2023-06-23

Changed

  • Flop.count/3 will now wrap queries that have GROUP BY clauses in a
    subquery.

Fixed

  • Fixed cursor-based pagination on composite types.
4 Likes
22

Hi, I’m currently giving your library a try, and I’m really impressed so far, so a big thank-you!

Currently, I’m having a bit of an issue trying to get compound keys to work, because for some reason it tries to query the database directly. I’m not sure where I’m branching off from the documentation.

Update

It seems to work fine, if I call Flop.validate_and_run/3

Code 
# In the schema
  @derive {
    Flop.Schema,
    sortable: [:first_name, :last_name],
    filterable: [:dairy_id, :search],
    default_order: %{
      order_by: [:last_name, :first_name],
      order_directions: [:asc, :asc]
    },
    compound_fields: [search: [:first_name, :last_name, :email, :phone_number, :id]]
  }

# Flop (created by Flop.validate/2)
%Flop{
  after: nil,
  before: nil,
  first: nil,
  last: nil,
  limit: 50,
  offset: nil,
  order_by: [:last_name, :first_name],
  order_directions: [:asc, :asc],
  page: nil,
  page_size: nil,
  filters: [%Flop.Filter{field: :search, op: :ilike, value: "elm"}]
}

# Query error
** (Postgrex.Error) ERROR 42703 (undefined_column) column m0.search does not exist

    query: SELECT ... FROM "members" AS m0 WHERE (m0."search" ILIKE $1) ORDER BY m0."last_name", m0."first_name" LIMIT $2
23

You didn’t post the actual function call you make, but usually this means that you forgot to pass the for option to the function that makes the query:

opts = [for: Pet]
flop = Flop.validate!(params, opts)
Flop.run(query, flop, opts)

And similarly with any other function that makes a query. Without this option, Flop doesn’t know in which schema to look up the field configuration, and it will just use the field name from the filter as it is.

Another issue I’m spotting in your code is that you added the id column to the compound field config. like only works on string (text, varchar) columns. Flop does not cast integer or binary columns as a string automatically, which means you’ll get a cast error here. If you want to allow partial matches on a binary ID column, the best way to do this at the moment would be a custom field.

1 Like
24

I see, I was looking at the docs for Flop.run/3 and thought this was handled automatically. Thank you for the prompt reply! :smile:

25

I can see how that can be confusing. I updated the example in the documentation and added some notes about the for option here and there: docs/run for by woylie · Pull Request #352 · woylie/flop · GitHub. I hope this will make it clearer for the next one. Thanks for bringing this up!

3 Likes
26

Flop version 0.21.0 is now available. This release brings significant enhancements to the casting and validation of filter values. These changes might require some adjustments to your schema configuration and to code that directly handles filter values. Please carefully review the upgrade notes linked below.

Added

  • Introduced operators as a new option for restricting acceptable operators
    for a custom field.
  • Added bindings option for custom fields, allowing required named bindings to
    be added via Flop.with_named_bindings/4.
  • The ecto_type option on join and custom fields now supports
    references: {:from_schema, MySchema, :some_field}.
  • The ecto_type option now supports a convenient syntax for adhoc enums:
    {:ecto_enum, [:one, :two]}.
  • Improved documentation with added type definitions: t:Flop.Schema.option/0,
    t:Flop.Schema.join_field_option/0, t:Flop.Schema.custom_field_option/0,
    and t:Flop.Schema.ecto_type/0, describing options available when deriving
    the Flop.Schema protocol.

Changed

  • Breaking change: Filter values are now dynamically cast based on the
    field type and operator, instead of allowing any arbitrary filter value. This
    change ensures that invalid filter values cause validation errors instead of
    cast errors.
  • The options for deriving the Flop.Schema protocol and for use Flop
    now undergo stricter validation with NimbleOptions.
  • Flop.Cursor.encode/1 now explicitly sets the minor version option for
    :erlang.term_to_binary/2 to 2, aligning with the new default in OTP 26.
    Before, this option was not set at all.
  • Added a decoded_cursor field to the Flop struct. This field temporarily
    stores the decoded cursor between validation and querying and is
    discarded when generating the meta data.

Deprecated

  • The tuple syntax for defining join fields has been deprecated in favor of a
    keyword list.

Fixed

  • Resolved an issue where setting replace_invalid_params to true still
    caused validation errors for pagination and sorting parameters due to cast
    errors, instead of defaulting to valid parameters.
  • Fixed the type specification for Flop.Filter.allowed_operators/1.
4 Likes