Filtering on date/datetime columns to fetch "not between" given dates

Is your feature request related to a problem? Please describe.

I have a use-case where I want to apply a filter on a date column to find all the results NOT ON a given date.

To give an example of the SQL I would like to run it would look something like

SELECT * from table where my_date_column > "2022-12-01" OR my_date_column < "2022-12-01"

At the moment the flop filters are all applied using an AND clause (which makes complete sense). I can see that some filters have an _or variant but that specifically applies to the like/ilike case.

Describe the solution you'd like
I'd be curious to hear if you have any suggested ways to implement this type of date filtering using the existing functionality in Flop.

I also wonder whether we could add between and not_between. This could apply to dates, datetimes, integers, floats.

Describe alternatives you've considered
I haven't been able to find any solutions which use Flop - The only alternative for me would be handling this specific use-case separately in my code and writing the query from scratch.

Thanks 🙌

SELECT * from table where my_date_column > "2022-12-01" OR my_date_column < "2022-12-01"

That would just be my_date_column != "2022-12-01", or am I missing something?

I'd be curious to hear if you have any suggested ways to implement this type of date filtering using the existing functionality in Flop.

It would be nice if Flop could support combining filters with or, but I'm not sure how the API for that would look like. We would need to find a parameter format. The Flop parameters are designed in a way that they can be expressed as URL query parameters. Also, it should be possible to express the or in a filter form with Flop.Phoenix.filter_fields/1. I'm afraid that adding that feature would make things complicated and hard to comprehend.

I also wonder whether we could add between and not_between. This could apply to dates, datetimes, integers, floats.

That might be possible. I'm wondering what the best value format for that filter would be. We cannot use a tuple, since you cannot put a tuple in a query parameter. So it would have to be either a list with two elements, or a map like %{min: x, max: y}.

Describe alternatives you've considered I haven't been able to find any solutions which use Flop - The only alternative for me would be handling this specific use-case separately in my code and writing the query from scratch.

The current main branch has a new feature called "custom fields", with which you can define your own filter conditions:

flop/lib/flop/schema.ex

Line 355 in c428af0

## Custom fields

You should be able to express the between and not_between filters that way.

That would just be my_date_column != "2022-12-01", or am I missing something?

Brain fart on my part - a single date was not the right example. A date range or datetime would have been a better example.

The current main branch has a new feature called "custom fields", with which you can define your own filter conditions:

Ahh! yeah I didn't think about that. I'm actually already using custom fields on a jsonb column for something else so I think for now this will solve the immediate problem.

Supporting combining filters with or would definitely be nice, but I fully appreciate the added complexity that adds to the API and the implementation