rcdilorenzo / filtrex Goto Github PK

Instead of relying on fancy-formatted maps for passing into the functions, the passed config should be refactored into an actual struct where it will be easy to specify different options for different keys but the same type (e.g. a float and a integer for #8).

Hi,

I am using filtrex in phoenix. I am receiving following params for filtering:

%{"status_equals"=>1,"name_contains"=>"joe black"}

I have three fields in the table: status (integer), first_name (string), last_name (string). I want to be able to join first name and last name to carry out the search:
status_equals=1 AND concatenate(first_name, last_name)_contains="joe black")

There are more filters on the page and hence, the combination of filters coming in params would keep changing.

Thanks in advance.

This is because it is recommended to be storing dates in UTC rather than in a particular time zone. This includes the following comparators for Filtrex.Condition.Date: in the last, not in the last, in the next, not in the next.

Because of the convention of ! meaning raise exception, it will be counterintuitive to use parse! when it catches exceptions instead of throwing them.

(CC: Relates to #25 by @danielberkompas)

It would be useful if you could filter on multiple columns with an or query
i.e. 'name_or_email_contains' which would find all of the users whose name or email contains the passed in string.

Covers #23

It would be much nicer if an existing Ecto.Query could be piped into the Filtrex.query/2 function like

query = from(m in Filtrex.SampleModel, where: m.rating > 90)
  |> Filtrex.query(filter)

Happening with all filters.

** (ArgumentError) argument error
        (stdlib 3.13) :ets.member(Mix.Compilers.ApplicationTracer, Ecto.Query)
        (mix 1.11.0) lib/mix/compilers/application_tracer.ex:31: Mix.Compilers.ApplicationTracer.trace/2
        (elixir 1.11.0) src/elixir_env.erl:36: :elixir_env."-trace/2-lc$^0/1-0-"/3
        (elixir 1.11.0) src/elixir_env.erl:36: :elixir_env.trace/2
        (elixir 1.11.0) src/elixir_dispatch.erl:189: :elixir_dispatch.expand_require/5
        (elixir 1.11.0) src/elixir_dispatch.erl:115: :elixir_dispatch.dispatch_require/6
        (elixir 1.11.0) src/elixir.erl:332: :elixir.quoted_to_erl/3 
        (elixir 1.11.0) src/elixir.erl:249: :elixir.eval_forms/3
        (elixir 1.11.0) lib/code.ex:700: Code.eval_quoted/3
        (filtrex 0.4.3) lib/filtrex.ex:115: Filtrex.query/3

Hi,

I am trying to create filters on a number field but keep encountering error with message no with clause matching: {:error, "Invalid number value for '2'"}

url: http://localhost:5000/privileged/candidates?access_id=1ae51ba3-ee00-70b9-bc4a-57e99aaaf05b&page=3&status_equals=2

model - candidate.ex

 defmodule MyApp.Members.Candidate do
   use Ecto.Schema
   use Arc.Ecto.Schema
   import Ecto.Changeset
   import Filtrex.Type.Config

...

  def filter_options(:privileged) do
    defconfig do
      number :status, allow_decimal: false
    end
  end
...

candidates_controller.ex

  ...
  {:ok, filter} <- Filtrex.parse_params(Candidate.filter_options(:privileged),
                                               Map.drop(params, ~w(access_id page))) do
  ...
    candidates =
      query
      |> Members.batch_candidates(batch.id)
      |> Members.preload_nominations_for_candidates()
      |> Filtrex.query(filter)
      |> Repo.paginate(Map.put(params, :page_size, 8)) 
  ...

What am I doing wrong?

I think this is a pretty low effort based on this. Mostly updating tests?

I'm getting Failed to use "ecto" (version 3.0.4) because filtrex (versions 0.4.1 and 0.4.2) requires ~> 2.1

From the documentation, it's difficult to tell how to filter/query on associated records. Is that part of the feature set? If so, I'll be happy to provide some documentation/contribution.

It also might be nice to have an Ecto type that could automatically serialize to and from a JSONB-like database field.

Originally posted by @rcdilorenzo in #42 (comment)

This module would not only validate that the value is a boolean but would parse something like "flag=" to be false once #4 is finished.

I think this line always causes a parsing failure on any integer value. It immediately fails when(both condition must be satisfied):

1. options are configured with allow_decimal: false
2. value (to be parsed) is integer %{"age_equals" => 1}

Provided fix in #49

For example:

def index(conn, params)
  filter_params = Map.drop(params, ~w(account_id))
  # ...
end

Why not ignore keys not presence in Model.filter_options by default? That way it doesn't raise an error when unknown key-value pairs are used.

It doesn't look like it, but I want to be sure.

Really like the library but have not been able to see how to do searches on associations which is huge need when using a relational database. Many thanks.

When trying to filter inserted_at=nil it returns Timex error Expected 4 digit year, but got `n` instead.
example url: /posts?_utf8=✓&post%5Binserted_at%5D=nil

Steps to reproduce:

Filtrex.parse_params([ %Filtrex.Type.Config{keys: ["inserted_at"], options: %{}, type: :datetime} ], %{"inserted_at" => "nil"}

will return

{:error, "Expected 4 digit year, but got `n` instead."}

Generated query:

#Ecto.Query<from t in App.Model, where: is_nil(t.deleted_at), where: fragment("")>

** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR (syntax_error): syntax error at or near ")"
        (ecto) lib/ecto/adapters/sql.ex:185: Ecto.Adapters.SQL.query!/5
        (ecto) lib/ecto/adapters/sql.ex:481: Ecto.Adapters.SQL.execute/6
        (ecto) lib/ecto/repo/queryable.ex:95: Ecto.Repo.Queryable.execute/5
        (ecto) lib/ecto/repo/queryable.ex:15: Ecto.Repo.Queryable.all/4

As we have updated deps and some code, can you update the hex release?

A particular type of JSON format must be followed to construct a filter. However, no method currently exists to dump a filter to a JSON-encodable format that can be later re-serialized into a filter. The following should work:

import Filtrex.Type.Config
config = defconfig do
  text :title
end

{:ok, filter} = Filtrex.parse_params(config, %{"title_contains" => "Buy"})
assert Filtrex.dump(filter) == %{
  "filter" => %{
    "type" => "all",
    "conditions" => [
      %{"column" => "title", "comparator" => "contains",
        "value" => "Buy", "type" => "text"}
    ],
  "sub_filters" => []
}

Because it is currently parsing the AST and assuming Ecto, it would be good to actually list it as a dependency but make it optional.

Currently, the following is required to generate an Ecto.Query from a filter:

Filtrex.query(filter, YourApp.YourModel, __ENV__)

This is not a nicely readable call. Instead, it would be nicer to not require passing in the __ENV__:

Filtrex.query(filter, YourApp.YourModel)

Hi,

is there a support for setting a different field name for Ecto Query's where condition, so that it does not have to match the public/exposed parameter name? Imagine an API which has one field name exposed to the outside world, but another field name used internally by Ecto.

Thanks.

When attempting to filter a query with joins on inserted_at I get a (Postgrex.Error) ERROR 42702 (ambiguous_column): column reference "inserted_at" is ambiguous as the fragment doesn't specify which table it's referencing.

When do you plan to release v0.3.0 on Hex? I'm building a library that depends on Filtrex, and until v0.3.0 is out, people who use my library will have to add filtrex to their mix.exs using a git reference.

I am using DateTime between condition with inserted_at field but got error.

 {:error, "Unknown filter key 'inserted_at_between'"}

Config

defconfig do
  datetime :inserted_at
end

If an error happens with either of these calls, they will indicate in the Filtrex struct that the filter is empty:

%Filtrex{empty: true}

When this value is piped into Filtrex.query/2, it will by default not narrow the query but could be configurable to always return an empty set of values:

# default behavior:
Filters.query(YourApp.YourModel, %Filtrex{empty: true})
# => does not narrow query

# optional behavior
Filters.query(YourApp.YourModel, %Filtrex{empty: true}, allow_empty: false)
# => returns query that never matches (e.g. true == false or something similar)

This is creating a dialyzer issue: 392a87a

It would be cool if we release a new version with this fixed (that is already on master).

Thanks!

Since all of the conditions already have equals and does not equal, the comparators is and is not can be removed.

Hi. I was addressed to your library from the elixir forum, where I asked this question on parsing and executing complex queries.
I have not yet started using your library, just skimmed through the docs, and I have a question regarding the syntax for filters like field >= value.
I come from SQLAlchemy, then very briefly moved through Django, and now I landed in the Elixir environment.
in Django they have a very nice syntax for that sort of filters, and very generic, based on double underscores __.

for example, a filter on an hypothetical publisher table, only those with books rated more than 5: filter=Q(book__rating__gt=5)

written this way it's easier to split the components.

the trailing compact comparison identifiers, they come from the well known set gt lt / lte gte. (actually, I prefer the even more compact and complete: gt lt eq / le ge ne - even if the eq is redundant.)

I'm curious about your ideas. and if you want to comment on the elixir forum, please!

Right now, the condition modules are strictly based on the type key-value in the map structure being passed in. Due to #4, it would be easier and more flexible to configure a list of these modules that the params could be passed through. This feature would also allow developers to easily extend the library to support their own conditions. This module-type configuration is similar to rcdilorenzo/repeatex helper.ex.

This feature would allow passing an Timex date format (e.g. "{MM}-{DD}-{YYYY}") to the date condition to be able to parse different formats into something that adapters can handle.

Currently, it is required to have a complete data structure in order to construct a query from a client. However, this is impractical when trying to do a GET request with filter type parameters. Using the validation and existing features of the library, it would be very helpful to be able to parse parameters such as the following:

/endpoint?completed_date_at_or_after=2016-03-10&title_contains=blah&status=completed

into something like

%Filtrex{type: "all", conditions: [
  %Filtrex.Condition.Date{column: "completed_date", comparator: "at or after", value: "2016-03-10"},
  %Filtrex.Condition.Text{column: "status", comparator: "equals", value: "completed"},
  %Filtrex.Condition.Text{column: "title", comparator: "contains", value: "blah"}
]}

This parsing would allow use of the complex ecto queries from url parameters and yet allow for customizability.

The additional verbiage of "or_after" or "contains" would be parsed specifically by the condition type and would still have the whitelist configuration for allowed keys on the model that the existing parse call has.

Right now, we have a list of configurations that are created by just typing in the appropriate structs like this:

config = [
  %Filtrex.Type.Config{type: :text, keys: ~w(title comments)},
  %Filtrex.Type.Config{type: :date, keys: ~w(posted_at), options: %{format: "{0M}-{0D}-{YYYY}"}}
]

However, it is just assumed that the user of this library will follow the appropriate options such as that date format configuration. Instead of assuming, it would be better to validate at compile time that these follow the specific conditions options. Here's a rudimentary spec for how this might be used:

defmodule MyApp.Post do
  require Filtrex

  def filter_config(user_id) do
    Filtrex.defconfig do
      text ~w(title comments)
      date ~w(posted_at), format: "{0M}-{0D}-{YYYY}"
      number ~w(user_id), allowed_values: [user_id]
    end
  end
end

This would simply be syntactical sugar. To maintain backwards compatibility and make the syntax optional, it would compile down to a list of configs as in the way configs must be currently be created.

Currently, parsing params returns errors in this format:

{:error, "Unknown filter key 'title_means'"}

However, the main parse method returns like this when the structure is invalid.

All of these functions should return a single error to be consistent as they all exit immediately when any error occurs.

I like the idea of this package. Please take the following as constructive and not negative :)

1. The title "Parsing Filters from URL Params" is confusing, and perhaps a little uninteresting. What filter are we parsing? What do I want to parse a filter? Do you mean "Build filer from Params"? Furthermore, I'm not sure why I would even be interested in the filter, aside from the validation aspect. Perhaps thats a question that could be answered in the readme :)

2. Forgive my ignorance, but I'm unfamiliar with a "smart filter". Perhaps that should be explained.

3. In this example,

   query = from(s in YourApp.YourModel)
     |> Filtrex.query(filter)  # => #Ecto.Query<...
   

   why would someone do this? What would I just not do YourApp.YourModel |> Filtrex.query(filter)?

4. I had to read section "Parsing Filter Structures" about 5 time before I realized what the section was describing. Again, I'm hung up on the verb "Parsing". If I put on my compiler hat, then I get the verb "Parsing". But it took me a while to get there. What I think is missing is some leading description that explains:
   a. two stop process. build filter, then build query
   b. filter can be created from controller params, or from more specific JSON like descriptor (needs better working, but I hope you get the point)

5. I would have one of your examples use the Ecto macro syntax. For example

   query = from(m in YourApp.YourModel, where: m.rating > 90)
     |> Filtrex.query(filter)  # => #Ecto.Query<...
   

   could be written as:

   query = YourApp.YourModel
     |> where([m], m.rating > 90)
     |> Filtrex.query(filter)
   

6. I was struggling with the emphasis on validation. At first, I would not see the use case since I was thinking of a static filter page (list the model fields and edit controls for each). However, I just saw the potential for a user created filter (like a query language) and realize where validation would be a neat feature. I guess my point, is that there may be two use cases here and I missed the second completely. Or am I still missing something?

The more I review this and think about it, the more potential I see with it. I think you have a useful package here.

Hope I was not too critical.

Steve

Most clients submitting JSON will be using string keys. To mitigate an atom conversion attack, the library should support converting to atoms but only allowing certain string names to be converted or else return an error that the filter structure is invalid.

In addition to #4, more flexibility could be added if a special parameter called filter_union to affect the union filter type (i.e. all, any or none) where all is the default. It could be added anywhere in the query parameters as in the following example:

/endpoint?title_contains=blah&status=completed&filter_union=any

The above call should generate something like:

%Filtrex{type: "any", conditions: [
  %Filtrex.Condition.Text{column: "status", comparator: "equals", value: "completed"},
  %Filtrex.Condition.Text{column: "title", comparator: "contains", value: "blah"}
]}

Text filter is case-sensitive and filters for complete text match only at present. It would be very useful if there is an option to make it case insensitive and do partial matches as well.

Default values can be case_sensitive: true and match: exact but we could add options like below:

text :name, case_sensitive: false, match: partial

Hi,

I have setup a filter as:

number :status, allow_decimal: false, allowed_values: 1..5

if no status is selected in filters, blank value is passed in params. I am filtering the blank values before sending the filter params to Filtrex.parse_params else it responds with an error. This pattern is so common, I was wondering if it would be good idea to include this as an option in the config - something like:

number :status, allow_decimal: false, allowed_values: 1..5, ignore_blanks: true