woylie/flop

Filtering, ordering and pagination for Ecto

flop's Issues

configure total_count

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

improve docs for parameter formats

define default order

allow to define default limit / page size

add types

Operator validation

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

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

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?

improve compile time config validation

validate configured fields etc.

require boolean value for empty/not_empty filters for easier integration with forms

Silently ignore invalid parameters

There should be an option to silently ignore invalid parameters instead of returning changeset errors.

better support for join queries

add debug log on validation error

Flop.validate!/2

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

accept filter as expression string

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

cursor-based pagination

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

set default pagination type

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

provide way to configure Flop for multiple apps in umbrella

support fragments when ordering

revisit empty/not_empty filters

• #194
• #225

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?

Example for Absinthe / Relay

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

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

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!

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.

Dynamic/Fragments

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

add instructions for meta data (count)

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.
________________________________________________________________________________

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.

set default page to 1

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?

Absinthe helpers

• function to turn a query result into Relay connection format

Get used bindings

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

allow passing page number as string to set_page

...and related functions

validate filter value types

allow to define max limit / max page size

Concatenated value comparison for compound fields

• Implement the remaining operators for compound fields: :==, :!=, :<=, :<, :>=, :>, :in

has_previous_page? / has_next_page? for opposite direction

At the moment, has_previous_page? is only set with :before, and has_next_page? is only set with :first. This is allowed according to the Relay specification, but it doesn't allow us to conditionally render a previous link.

How to use filters with phoenix no documentation at all

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

filter: LIKE '%something%'

document parameters with query string examples

• add docs for ordering
• add docs for filtering
• add docs for pagination

Phoenix helpers

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

test filtering with different data types

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.

filter: in