Giter Club home page Giter Club logo

flop's Introduction

Flop

CI Hex codecov

Flop is an Elixir library designed to easily apply filtering, ordering, and pagination to your Ecto queries.

Features

  • Offset-based pagination: Allows pagination through offset/limit or page/page_size parameters.
  • Cursor-based pagination: Also known as key set pagination, provides a more efficient alternative to offset-based pagination. Compatible with Relay pagination arguments.
  • Sorting: Applies sort parameters on multiple fields in any direction.
  • Filtering: Allows complex data filtering using multiple conditions, operators, and fields.
  • Parameter validation: Ensures the validity of provided parameters.
  • Configurable filterable and sortable fields: Only applies parameters to the fields that were explicitly configured as filterable or sortable.
  • Join fields: Allows the application of pagination, sort, and filter parameters on any named binding. Provides functions to help you to avoid unnecessary join clauses.
  • Compound fields: Provides the ability to apply filter parameters on multiple string fields, for example for a full name filter.
  • Custom fields: Provides an escape hatch for filters that Flop is not able to build on its own.
  • Relay connection formatter: Formats the connection in Relay style, providing edges, nodes, and page info.
  • UI helpers and URL builders through Flop Phoenix: Pagination, sortable tables and filter forms.

Installation

To get started, add flop to your dependencies list in your project's mix.exs file:

def deps do
  [
    {:flop, "~> 0.25.0"}
  ]
end

You can also configure a default repo for Flop by adding the following line to your config file:

config :flop, repo: MyApp.Repo

Instead of configuring Flop globally, you can also use a configuration module. Please refer to the Flop module documentation for more information.

Usage

Define sortable and filterable fields

To define sortable and filterable fields in your Ecto schema, you can derive Flop.Schema. This step is optional but highly recommended, particularly when the parameters passed to Flop's functions are user-provided. Deriving Flop.Schema ensures that Flop applies filtering and sorting parameters only to the fields you've explicitly configured.

defmodule MyApp.Pet do
  use Ecto.Schema

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

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

Besides sortable and filterable fields, Flop.Schema also allows the definition of join fields, compound fields, or custom fields. You can also set maximum or default limits, among other options. For a comprehensive list of available options, check the Flop.Schema documentation.

Query data

Use the Flop.validate_and_run/3 or Flop.validate_and_run!/3 function to both validate the parameters and fetch data from the database, and acquire pagination metadata in one operation.

Here is an example of how you might use this in your code:

defmodule MyApp.Pets do
  import Ecto.Query, warn: false

  alias Ecto.Changeset
  alias MyApp.{Pet, Repo}

  @spec list_pets(map) ::
          {:ok, {[Pet.t()], Flop.Meta.t()}} | {:error, Flop.Meta.t()}
  def list_pets(params \\ %{}) do
    Flop.validate_and_run(Pet, params, for: Pet)
  end
end

The for option sets the Ecto schema for which you derived Flop.Schema. If you haven't derived Flop.Schema as described above, this option can be omitted. However, this is not recommended unless all parameters are generated internally and are guaranteed to be safe.

On success, Flop.validate_and_run/3 returns an :ok tuple. The second element of this tuple is another tuple containing the fetched data and metadata.

{:ok, {[%Pet{}], %Flop.Meta{}}}

You can learn more about the Flop.Meta struct in the module documentation.

Alternatively, you may separate parameter validation and data fetching into different steps using the Flop.validate/2, Flop.validate!/2, and Flop.run/3 functions. This allows you to manipulate the validated parameters, to modify the query depending on the parameters, or to move the parameter validation to a different layer of your application.

with {:ok, flop} <- Flop.validate(params, for: Pet) do
  Flop.run(Pet, flop, for: Pet)
end

The aforementioned functions internally call the lower-level functions Flop.all/3, Flop.meta/3, and Flop.count/3. If you have advanced requirements, you might prefer to use these functions directly. However, it's important to note that these lower-level functions do not validate the parameters. If parameters are generated based on user input, they should always be validated first using Flop.validate/2 or Flop.validate!/2 to ensure safe execution.

The examples above assume that you configured a default repo. However, you can also pass the repo directly to the functions:

Flop.validate_and_run(Pet, flop, repo: MyApp.Repo)
Flop.all(Pet, flop, repo: MyApp.Repo)
Flop.meta(Pet, flop, repo: MyApp.Repo)

For more detailed information, refer the documentation.

Parameter format

The Flop library requires parameters to be provided in a specific format as a map. This map can be translated into a URL query parameter string, typically for use in a web framework like Phoenix.

Pagination

Offset / limit

You can specify an offset to start from and a limit to the number of results.

%{offset: 20, limit: 10}

This translates to the following query parameter string:

?offset=20&limit=10

Page / page size

You can specify the page number and the size of each page.

%{page: 2, page_size: 10}

This translates to the following query parameter string:

?page=2&page_size=10

Cursor

You can fetch a specific number of results before or after a given cursor.

%{first: 10, after: "g3QAAAABZAACaWRiAAACDg=="}
%{last: 10, before: "g3QAAAABZAACaWRiAAACDg=="}

These translate to the following query parameter strings:

?first=10&after=g3QAAAABZAACaWRiAAACDg==
?last=10&before=g3QAAAABZAACaWRiAAACDg==

Ordering

To sort the results, specify fields to order by and the direction of sorting for each field.

%{order_by: [:name, :age], order_directions: [:asc, :desc]}

This translates to the following query parameter string:

?order_by[]=name&order_by[]=age&order_directions[]=asc&order_directions[]=desc

Filters

You can filter the results by providing a field, an operator, and a value. The operator is optional and defaults to ==. Multiple filters are combined with a logical AND. At the moment, combining filters with OR is not supported.

%{filters: [%{field: :name, op: :ilike_and, value: "Jane"}]}

This translates to the following query parameter string:

?filters[0][field]=name&filters[0][op]=ilike_and&filters[0][value]=Jane

Refer to the Flop.Filter documentation and t:Flop.t/0 type documentation for more details on using filters.

Internal parameters

Flop is designed to manage parameters that come from the user side. While it is possible to alter those parameters and append extra filters upon receiving them, it is advisable to clearly differentiate parameters coming from outside and the parameters that your application adds internally.

Consider the scenario where you need to scope a query based on the current user. In this case, it is better to create a separate function that introduces the necessary WHERE clauses:

def list_pets(%{} = params, %User{} = current_user) do
  Pet
  |> scope(current_user)
  |> Flop.validate_and_run(params, for: Pet)
end

defp scope(q, %User{role: :admin}), do: q
defp scope(q, %User{id: user_id}), do: where(q, user_id: ^user_id)

If you need to add extra filters that are only used internally and aren't exposed to the user, you can pass them as a separate argument. This same argument can be used to override certain options depending on the context in which the function is called.

def list_pets(%{} = args, opts \\ [], %User{} = current_user) do
  flop_opts =
    opts
    |> Keyword.take([
      :default_limit,
      :default_pagination_type,
      :pagination_types
    ])
    |> Keyword.put(:for, Pet)

  Pet
  |> scope(current_user)
  |> apply_filters(opts)
  |> Flop.validate_and_run(flop, flop_opts)
end

defp scope(q, %User{role: :admin}), do: q
defp scope(q, %User{id: user_id}), do: where(q, user_id: ^user_id)

defp apply_filters(q, opts) do
  Enum.reduce(opts, q, fn
    {:last_health_check, dt}, q -> where(q, [p], p.last_health_check < ^dt)
    {:reminder_service, bool}, q -> where(q, [p], p.reminder_service == ^bool)
    _, q -> q
  end)
end

With this approach, you maintain a clean separation between user-driven parameters and system-driven parameters, leading to more maintainable and less error-prone code.

Relay and Absinthe

The Flop.Relay module is useful if you are using absinthe with absinthe_relay, or if you simply need to adhere to the Relay cursor specification. This module provides functions that help transform query responses into a format compatible with Relay.

Consider the scenario where you have defined node objects for owners and pets, along with a connection field for pets on the owner node object.

node object(:owner) do
  field :name, non_null(:string)
  field :email, non_null(:string)

  connection field :pets, node_type: :pet do
    resolve &MyAppWeb.Resolvers.Pet.list_pets/2
  end
end

node object(:pet) do
  field :name, non_null(:string)
  field :age, non_null(:integer)
  field :species, non_null(:string)
end

connection(node_type: :pet)

Absinthe Relay will establish the arguments after, before, first and last on the pets field. These argument names align with those used by Flop, facilitating their application.

Next, we'll define a list_pets_by_owner/2 function in the Pets context.

defmodule MyApp.Pets do
  import Ecto.Query

  alias MyApp.{Owner, Pet, Repo}

  @spec list_pets_by_owner(Owner.t(), map) ::
          {:ok, {[Pet.t()], Flop.Meta.t()}} | {:error, Flop.Meta.t()}
  def list_pets_by_owner(%Owner{id: owner_id}, params \\ %{}) do
    Pet
    |> where(owner_id: ^owner_id)
    |> Flop.validate_and_run(params, for: Pet)
  end
end

Now, within your resolver, you merely need to invoke the function and call Flop.Relay.connection_from_result/1, which transforms the result into a tuple composed of the edges and the page_info, as required by absinthe_relay.

defmodule MyAppWeb.Resolvers.Pet do
  alias MyApp.{Owner, Pet}

  def list_pets(args, %{source: %Owner{} = owner} = resolution) do
    with {:ok, result} <- Pets.list_pets_by_owner(owner, args) do
      {:ok, Flop.Relay.connection_from_result(result)}
    end
  end
end

In case you want to introduce additional filter arguments, you can employ Flop.nest_filters/3 to convert simple filter arguments into Flop filters, without necessitating API users to understand the Flop filter format.

Let's add name and species filter arguments to the pets connection field.

node object(:owner) do
  field :name, non_null(:string)
  field :email, non_null(:string)

  connection field :pets, node_type: :pet do
    arg :name, :string
    arg :species, :string

    resolve &MyAppWeb.Resolvers.Pet.list_pets/2
  end
end

Assuming that these fields have been already configured as filterable with Flop.Schema, we can use Flop.nest_filters/3 to take the filter arguments and transform them into a list of Flop filters.

defmodule MyAppWeb.Resolvers.Pet do
  alias MyApp.{Owner, Pet}

  def list_pets(args, %{source: %Owner{} = owner} = resolution) do
    args = nest_filters(args, [:name, :species])

    with {:ok, result} <- Pets.list_pets_by_owner(owner, args) do
      {:ok, Flop.Relay.connection_from_result(result)}
    end
  end
end

Flop.nest_filters/3 uses the equality operator :== by default. You can override the default operator per field.

args = nest_filters(args, [:name, :species], operators: %{name: :ilike_and})

Flop Phoenix

Flop Phoenix is a companion library that provides Phoenix components for pagination, sortable tables, and filter forms, usable with both Phoenix LiveView and in dead views. It also defines helper functions to build URLs with Flop query parameters.

flop's People

Contributors

aej avatar andreasknoepfle avatar ashabhasa avatar blakedietz avatar brianphilips avatar bunker-inspector avatar daeddy avatar dependabot-preview[bot] avatar dependabot[bot] avatar elridion avatar fredwu avatar kianmeng avatar linusdm avatar maltoe avatar michallepicki avatar mmore avatar treere avatar vanderhoop avatar woylie avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar

flop's Issues

configure total_count

  • add option to disable total_count query
  • disable total_count query by default if using cursor-based pagination

Support query prefix option

Hey @woylie ๐Ÿ‘‹

This is an amazing start for a library. I'm super impressed with the code and the general direction this library takes. I know it's still in early development, but I would bet it's here to stay. ๐Ÿ’š

I'm currently evaluating using Flop in a new project instead of our old messy custom filtering code. It looks super promising so far, but one critical feature I'm missing is support for Ecto's :prefix option to set the default query prefix. As far as I can tell, it shouldn't be too difficult to add support for it, and I hope you can see its usefulness. I already started a tiny patch, will submit the PR in a minute.

Thanks for the awesome work on this library!

Best,
malte

Typespec incorrect for Flop.Schema.apply_order_by

Hi ๐Ÿ‘‹

Since #138 we see a dialyzer error originating in Flop.Schema.apply_order_by/3. We have a compound field based on a join field.

The function call will not succeed.

Flop.Schema.apply_order_by(_ :: any(), _ :: any(), {_, _})

breaks the contract
(any(), Ecto.Query.t(), :elixir.keyword()) :: Ecto.Query.t()

Best,
malte

Compund fields clash with `Ecto.Changeset` import

Hi there ๐Ÿ‘‹

Describe the bug
I noticed that once I try to use the new compound fields in an Ecto.Schema, that imports Ecto.Changeset I get the following error.

 ** (CompileError) .../my_schema.ex:22: imported Ecto.Changeset.get_field/2 conflicts with local function
    (elixir 1.12.2) src/elixir_locals.erl:94: :elixir_locals."-ensure_no_import_conflict/3-lc$^0/1-0-"/2
    (elixir 1.12.2) src/elixir_locals.erl:95: anonymous fn/3 in :elixir_locals.ensure_no_import_conflict/3
    (flop 0.13.1) .../my_schema.ex:22: Flop.Schema.Any.__deriving__/3

To Reproduce
Essentially just import Ecto.Changeset and use the @derive directive with the new compound fields like so:

  use Ecto.Schema
  import Ecto.Changeset # <-- this one

  @derive {Flop.Schema,
           filterable: [:full_name],
           sortable: [:full_name],
           compound_fields: [full_name: [:first_name, :last_name]]}

Expected behavior
It does not clash with Ecto.

Versions:

  • Flop: 0.13.1
  • Elixir: 1.12.2
  • Erlang: 24.0.5
  • Ecto: 3.7.1
  • Ecto SQL: 3.7.0

Additional context
One can workaround this by importing / reimporting Ecto.Changeset without get_field (even works when a macro previously imported all of Ecto.Changeset).

  import Ecto.Changeset, except: [get_field: 2]

Thank you for creating & maintaining flop btw ๐Ÿ’š . It helps us a ton and we are soon taking it to production ๐Ÿšข

Cheers
Andi

Compound fields

To support filters that work across multiple fields, Flop.Schema should allow to define compound fields.

defmodule MyApp.User do
  use Ecto.Schema

  @derive {Flop.Schema, compound_fields: [name: [:family_name, :given_name]}

  schema "users" do
    field :family_name, :string
    field :given_name, :string
    field :email, :string
  end
end

By passing a filter on the field :name, Flop would add a WHERE clause on both fields. When using simple filters, Flop would default to the :ilike_or operator.

The default operator could be set with the :default_operators option:

@derive {Flop.Schema,
                compound_fields: [name: [:family_name, :given_name],
                default_operators: [name: :ilike_and]}

The clauses on each field should be connected with an OR, independent of the operator.

# search term: 'margo martindale'
# operator: :ilike_or
WHERE family_name ilike '%margo%'
OR given_name ilike '%margo%'
OR family_name ilike '%martindale%'
OR given_name ilike '%martindale%'

# search term: 'margo martindale'
# operator: :ilike_and
WHERE (family_name ilike '%margo%' OR given_name ilike '%martindale%')
AND (family_name ilike '%margo%' OR given_name ilike '%martindale%')
  • add config for compound fields to Flop.Schema
  • allow filtering by compound fields

fix dialyzer warnings (no local return)

lib/schema.ex:6:no_return
Function filterable/1 has no local return.
________________________________________________________________________________
lib/schema.ex:7:no_return
Function sortable/1 has no local return.
________________________________________________________________________________

Query helpers

It might be nice to have some wrapper functions to get the results and the meta data in one function call, to wrap a query into Flop validation/query.

Default filter operators

Support passing filters as a keyword list or map without the need to specify an operator.

params = %{filters: %{title: "Psycho"}}
Flop.validate_and_run(Movie, params, for: Movie)

The default operator would be :==, but could be customized with the default_operators option of Flop.Schema.

@derive {Flop.Schema,
                filterable: [:title],
                default_operators: [title: :ilike_and]}
  • make operator optional, use :== as default
  • allow passing default operators per field as option to query functions
  • allow specifying default operators per field in Flop.Schema
  • hide default operator parameters in query parameter list
  • allow passing filters as key/value map (can be accomplished with nest_filters/3

Support for joins

Is your feature request related to a problem? Please describe.
I have an HTML table that displays information from multiple db tables. When using the sort functionality from FlopPhoenix I'd like to specify a column to sort on that is not part of the main table.

Describe the solution you'd like
I believe there were plans for this in #48 and we've raised it in other tickets since then for the need to be able to have Flop be aware of other columns from passed in schemas/models.

Describe alternatives you've considered
Nothing.

Additional context
Let me know what you think @woylie. Looks like this has been on your radar for some time now. Wondering if you had any implementation ideas since you first created the original ticket here and the time you've spent with FlopPhoenix since then? Maybe there is some support already there that I can take advantage of?

Flop option to pass opts to `Ecto.Repo`

Ecto.Repo callback offer the possibility to pass on opts when executing queries. Query API
It would be nice to be able to pass those options through Flop when a project uses options apart from the basic ones which are already supported by Flop like :prefix and such.

defmodule MyApp.Pets do
  import Ecto.Query, warn: false

  alias Ecto.Changeset
  alias Flop
  alias MyApp.{Pet, Repo}

  @spec list_pets(Flop.t()) ::
          {:ok, {[Pet.t()], Flop.Meta.t}} | {:error, Changeset.t()}
  def list_pets(flop \\ %Flop{}) do
    Flop.validate_and_run(Pet, flop, for: Pet, query_opts: [special_flag: true])
  end
end

The underlying cause for the issue is the reliance on default query behaviour (for security reasons) that can be bypassed on a per-query basis using a special_flag.

I considered using a flag which is set directly in the process a later evaluated by the optional prepare_query callback. But feels a bit too "fire-and-forget"-ish.

Another idea was to write a extra Repo module which delegates most calls to the underlying Ecto.Repo leaving out the security feature, but this solution seems to be a bit too error prone since it would rely on internal repo functions.

Compound fields with joins

Is your feature request related to a problem? Please describe.
Now that we have compound and join fields it would be awesome to use them in conjunction, but I guess from what I can judge this is not possible yet.

  @derive {Flop.Schema,
           filterable: [:names],
           join_fields: [pet_name: {:pets, :name}],
           compound_fields: [:names [:name, :pet_name]]}

At the moment this leads (probably quite expectedly) to an error.
no function clause matching in Flop.Builder.get_field_type/2

Describe the solution you'd like
Having some way of specifying these would be great.

Describe alternatives you've considered

  @derive {Flop.Schema,
           filterable: [:names],
           compound_fields: [:names [:name, {:pets, :name}]]}

Could maybe also work as a way of specifying these ๐Ÿคท , but it also obviously doesn't work yet. Maybe easier to implement though? Not sure :D.

Or did I overlook something?

Thanks in advance โœŒ๏ธ
Cheers
Andi

Get used bindings

Add function that returns the named bindings required for a Flop query.

Filter for NULL values

Is your feature request related to a problem? Please describe.
I have a datetime field (e.g. deactivated_at) which can be NULL. I'd like to filter for all records which have a NULL value there. Or the opposite: have a value set there.

Describe the solution you'd like
Maybe just another operator which supports is_nil.

Or is there another good way to do it?

unify default order by config

Flop.Schema requires default order parameters to be set as separate keys default_order_by and default_order_directions, but this is converted into a default_order: %{order_by: list, order_directions: list}. This means you need to use the latter format to override the default in the opts (which is not documented). This should be unified, so that the same format is used everywhere.

Operator validation

  • validate filter operator depending on field type
  • expose function that returns allowed operators for a field

Help with accepting order_by params as array through URL

Hi Woylie I'm working on writing a wrapper function for sortable columns as we had spoke about before but running into a snag (I'm positive it's me here) when trying to form a querystring that includes order_by details that are placed into an array.

Simply approaching this from determining what the querystring should look like I have the following:

http://localhost.local:4000/fruits?page=2&order_by=[name]

Although when it hits the server the params are converted to:

%{"order_by" => "[name]", "page" => "2"}

Where "[name]" is not being accepted by Flops validation. I've tried different combinations of the order_by value but nothing seems to work.

What do you think? How should we be passing these array based values through from outside?

Cheers and thanks!

PS Any additional thoughts or time coming up to put into our other wishlist items?

Dynamic/Fragments

  • #127
  • support ordering by aggregates
  • support fragments when filtering?
  • support filtering by aggregates?

meta data

  • add Flop.Meta struct with total_count, total_pages, current_page, current_offset, has_previous_page?, has_next_page?, maybe page_size
  • add Flop.meta/3, which returns the meta struct for the given queryable and flop
  • add Flop.all_with_meta/3, which both the data and the meta data, and maybe validates the flop

Phoenix helpers

Helper functions for working with Phoenix and Phoenix templates. Should be part of a separate library.

set default pagination type

Option to set default pagination type, so that default limit does not automatically translate to a limit parameter.

Support for optional custom count query

Is your feature request related to a problem? Please describe.
When we have a number of complex queries that we use for pagination over a large data set through a pretty standard web ui (not LiveView in this case, but don't think there's a difference)

As the count query is just a wrapper around the given query (or in this case a replacement of the select columns and a removal of the order by) the count and planner will still likely consider any joins that are not important to the number of rows we're considering.

Describe the solution you'd like
Two things:

  1. The ability to provide a query to use for counting which will override the automatic wrapping/substitution of the main pagination query. In this case I will provide a new query without all the additional joins that are not needed. Heck I might also be ok that the custom count query doesn't return the exact number of records that I'm expecting it to, as long as it's a decent approximation - it's all on me at that point.

  2. In some cases as the dataset may grow even larger I would like to be able to disable the count query altogether. I'm already using first/previous/next/last links without all the in-between pages which would depend on knowing the amount of records overall. If the UX is done right, then typically a user is going to be provided a decent search box and only click through a couple pages of records if they can't find what they're looking for and then will go back and refine their search.

Describe alternatives you've considered
Lot's of playing around with indexes, some of what are useful, some not. It's really about being able to provide an alernate custom query for count to chew on.

Additional context
Have a look at Kaminari and will_paginate from the Ruby/Rails side as they offer these types of workarounds for larger and growing datasets.

Thanks Mattias!

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.