Grouping filters with AND/OR logic

[lucacorti]

Hi,

Flop it's proving really useful for us in contexts where queries need to be dynamically composable based on user input (e.g. API endpoints).

Currently we need to add some search functionality which needs to do an ilike_or on multiple columns for the same search string and return all results. It's my understanding filters in Flop are combined in an AND fashion, but in this case we would need something like:

WHERE some_filters AND (search_filter_1 OR search_filter_2 OR search_filter_3)

Which apparently is not doable with the standard filters API. Being able to combine filter groups in an OR fashion would prove useful. I hope I haven't missed something obvious.

I've worked around this by directly customizing the Ecto.Queryable passed to Flop before applying other filters, like the README suggests. I don't know if there's a better solution to this kind of problem. This is probably more a question about how you would solve this kind of use case rather than a feature request.

Thanks for the great library

[woylie]

Hey there,

Currently we need to add some search functionality which needs to do an ilike_or on multiple columns for the same search string and return all results. It's my understanding filters in Flop are combined in an AND fashion, but in this case we would need something like:

WHERE some_filters AND (search_filter_1 OR search_filter_2 OR search_filter_3)

You're right, currently it is not possible to combine filters with an OR. Maybe we'll get to that in a future version, although I'm not sure how this would look like without making the filter parameters cumbersome to work with.

In your specific case of an ilike_or filter applied on multiple columns, though, you can use compound fields. If you take the example from the docs:

@derive {
  Flop.Schema,
  filterable: [:full_name],
  sortable: [:full_name],
  compound_fields: [full_name: [:family_name, :given_name]]
}

And these filter parameters:

%{
  filters: [%{
    field: :full_name,
    op: :ilike_or
    value: "jo mal"
  }]
}

The filter would be applied on both columns:

WHERE
  (family_name ILIKE '%jo%' OR family_name ILIKE '%mal%') OR
  (given_name ILIKE '%jo%' OR given_name ILIKE '%mal%')

So you should be able to find John Malkovich this way.

Note that currently, the filter value is applied per field, though. If you change the operator to ilike_and, this would mean that either the family name must contain both tokens, or the given name must contain both tokens. In that case, you could find John Malkovich by searching for mal vich, but not by searching for joh kovi. There's an open issue to change the implementation to apply the filters on concatenated values instead: #120.

[lucacorti]

You're right, currently it is not possible to combine filters with an OR. Maybe we'll get to that in a future version, although I'm not sure how this would look like without making the filter parameters cumbersome to work with.

Yes, I can see how that would really complicate the api for a relatively rarer use case.

Note that currently, the filter value is applied per field, though. If you change the operator to ilike_and, this would mean that either the family name must contain both tokens, or the given name must contain both tokens. In that case, you could find John Malkovich by searching for mal vich, but not by searching for joh kovi. There's an open issue to change the implementation to apply the filters on concatenated values instead: #120.

Oh, I see. This should fit my current use case. Also, big Being John Malkovic fan 🙂

Thanks! ❤️

[lucacorti]

One more thing to add after trying to implement compound fields. This would work, however, one of the fields we need to search on is actually a float on the Ecto schema, so we would need to somehow cast it to a string.

Could this be solved by casting it to string in Flop?

[woylie]

One more thing to add after trying to implement compound fields. This would work, however, one of the fields we need to search on is actually a float on the Ecto schema, so we would need to somehow cast it to a string.

Could this be solved by casting it to string in Flop?

I've never seen an ilike on a float 😄

You would need to cast the field to a string indeed, but Flop cannot do such things at the moment. There is another open issue for derived fields, which would allow you to sort and filter by arbitrary query expressions: #118. You would be able to cast the field that way.

As a workaround, the only thing you could do right now is to join on a subquery or fragment and use a named binding. Then you can define a join field in your schema and add the join field to the compound field.

[lucacorti]

Yes, kind of strange implementation on a legacy system. Compound fields work great!