Using multiple filters for OR WHERE operations

[type1fool]

I've been using Flop for a few weeks, but I haven't figured out how to apply multiple filters for an OR WHERE query. The filters below appear to result in an AND WHERE query, where both rows must satisfy both ops:

%{
  filters: [
    %{
      field: :owner_id,
      op: :==,
      value: user_id
    },
    %{
      field: :visibility,
      op: :==,
      value: :public
    }
  ]
}

I've switched to using an Ecto.Query for my current needs. Is it possible to configure the filters above so that rows matching either filter would be returned?

[woylie]

Hi @type1fool, Flop does not support combining filters with or at the moment. There was a bit of discussion about this previously: #219

[type1fool]

Thank you, @woylie !

I can imagine how this would easily add complexity to the API. On that note, I wonder if grouping filters into keys like :one_of and :all_of would be reasonable. I think the similar pattern I've seen is from the Norm package.

Example

status = %{field: :status, op: :==, value: :active}
role = %{field: :role, op: :in, value: [:admin, :support]}

params = %{
  filters: [
    one_of: [status, role],
    all_of: [...]
  ]
}

This is a rough sketch from the back of a car. It might even make sense to allow nested one_of/all_of clauses.

[woylie]

Right. In principle, filters in a list are combined with and, and we'd want to keep that behavior for backward-compatibility. You could then say that a list item under filters can either be a Filter struct or something like a Combinator struct.

Operators omitted for brevity.

%Flop{
  filters: [
    %Filter{field: :a, value: 100},
    %Combinator{or: [%Filter{field: :b, value: 200}, %Filter{field: :c, value: 300}]}
  ]
}

Which would translate to a == 100 and (b == 200 or c == 300). Which would be equivalent to:

%Flop{
  filters: [
    %Combinator{and: [
      %Filter{field: :a, value: 100},
      %Combinator{or: [%Filter{field: :b, value: 200}, %Filter{field: :c, value: 300}]}
    ]}
  ]
}

In the examples above, the Combinator struct has two fields:

@type combinator :: %Combinator{and: [Filter.t()], or: [Filter.t()]}

Only one of them can be set.

The last example would translate into (maybe):

?filters[0][and][0][field]=a&filters[0][and][0][value]=100&filters[0][and][1][or][0][field]=b&filters[0][and][1][or][0][value]=200&filters[0][and][1][or][1][field]=c&filters[0][and][1][or][1][value]=300

An alternative structure for this struct would be:

@type combinator :: %Combinator{type: :and | :or, filters: [Filter.t()]}

The rewritten example for this structure:

%Flop{
  filters: [
    %Combinator{
      type: :and,
      filters: [
        %Filter{field: :a, value: 100},
        %Combinator{
          type: :or,
          filters: [
            %Filter{field: :b, value: 200},
            %Filter{field: :c, value: 300}
          ]
        }
      ]
    }
  ]
}

And as query parameters:

?filters[0][type]=and&filters[0][filters][0][field]=a&filters[0][filters][0][value]=100&filters[0][filters][1][type]=or&filters[0][filters][1][filters][0][field]=b&filters[0][filters][1][filters][0][value]=200&filters[0][filters][1][filters][1][field]=c&filters[0][filters][1][filters][1][value]=300

Some things to consider:

• Parameter validation is done with Ecto.Changeset, and if we want to keep it that way, we need to use maps and structs, not keyword lists.
• The format needs to map to URL query parameters, which means the struct name alone is not enough to differentiate between combinators (i.e. you couldn't use %Or{filters: []} and %And{filters: []} structs).
• Ideally, it should be possible to translate those filter parameters into an HTML form, and back, somehow.
• If we allow nesting, we need some sort of safeguard against unreasonable query complexity.

[aglassman]

First off, just want to say I really like Flop, and have been using it for many things over the past few months. I forked this repo, and implemented a very basic support for OR support.

There are gaps in what this approach can accomplish. It only allows one level of nesting, so it's not going to be a silver bullet, but meets all my needs I've run across so far.

My use case is "only retrieve rows where the current time is between the valid_start and valid_end timestamps. If valid_start or valid_end are null, treat them as an infinite range.

    flop_params = %{
      filters: [
        %{field: :active, op: :==, value: true},
        %{or: 1, field: :valid_start, op: :empty, value: true},
        %{or: 1, field: :valid_start, op: :<=, value: now},
        %{or: 2, field: :valid_end, op: :empty, value: true},
        %{or: 2, field: :valid_end, op: :>=, value: now}
      ]
    }

This format works fine with the current changeset implementation, and I assume would work fine with URL query parameters, and be pretty straightforward to generate HTML forms with it.

SELECT count(*) FROM "redemptions" AS r0 WHERE  (((r0."valid_start" IS NULL) = $2) OR (r0."valid_start" <= $3)) AND (((r0."valid_end" IS NULL) = $4) OR (r0."valid_end" >= $5)) AND (r0."active" = $6) [true, ~U[2023-04-28 16:04:00.549945Z], true, ~U[2023-04-28 16:04:00.549945Z], true]

The implementation groups the filters by the integer provided via the 'or' attribute on Filter, then creates a dynamic that 'ors' all the filters in that group, in the order defined. Then it will 'and' each group together. Finally, it applies all remaining filters as normal.

Right now the gap that I'll need to revise is that it groups all the non or'd filters together and applies them at the end. The fields should be applied in the defined order to take advantage of short circuiting if the underlying DB is implemented to do so.

I created a PR with my work so far if anyone would like to see my implementation, or thinks it would be helpful in moving towards Flop supporting ORs. This is just a rough draft I whipped up in an hour, so I'm not super invested in seeing this merged in or anything, just thought it may be helpful.
#449