Dedicated endpoint for filtering options

[dmos62]

At the moment we're introducing filtering options to the API, so that frontends can check what filtering options are available for a specific type. Currently, it's being introduced to the types endpoint (/databases/1/types/). But, that seems to not be a great idea. See the top-post in this discussion for an example of what this looks like. I don't want to post an example JSON here, since the format is still being discussed and I want to avoid putting WIP code on multiple discussions.

When using the types endpoint, we're exposing filtering options per Mathesar type. It's awkward, because some predicates (like and, or, not), are type-agnostic, but the only sensible way to put them on that endpoint is to repeatedly include them under every Mathesar type.

Alone, this seems like a small inconvenience, and it is, but this setup might become more burdensome in the future and I want to put it out there that there's the option to switch to a dedicated endpoint.

[kgodey]

@dmos62 It's hard for me to evaluate this without more details about what it will look like. What URL would you use, and what would an example response look like?

[mathemancer]

Warning: I've been thinking too much about this question on my morning walks.

Overall, I heartily support this. In fact, I came to the conclusion independently that attaching filtering options to types is too constraining in the long run. So I guess we're synced up there :)

Problem with filter options per-type: We need to be able to filter on functions of columns (e.g., filtering on the domain of an email). This is a bit tricky with the current format. In order to make it general, we need to be able to filter on functions that don't return the same output type as their input, and in fact on functions that take arbitrary input (probably requiring the row in question as input, though) and return arbitrary output.

Another concern with the current paradigm: We're arbitrarily deciding that the only valid comparisons (e.g., geq) are binary functions (here "binary" means two-input) from columns of the same type. What about A + B >= C? Even worse what about len(A) > C (here A could be a VARCHAR, and C an INTEGER). I don't think we need to support all possible comparisons and filters immediately, but our concept of filter should. I think we'll have real problems with that if the filters are attached to column types.

I think a filters endpoint that gives options of different functions that take different inputs, and return booleans solves lots of problems. (Comically, we'd need a way to filter that endpoint to restrict shown functions to what's wanted at the time).
These booleans could be combined (composed) using function composition. This puts not or xor on the same footing as geq. Even more general, we could have a "functions" endpoint, with some of the functions being filters. This would let us smoothly iterate from A > 0 as a filter to A > B, and then to A + B > C, and even on to len(A) + B > C, etc. We'd have A > 0 as a filter, and then add A > B, ADD, and LEN functions over time. The idea would be to think of filtering as just another composition of functions. To be more precise, the actual "filter" would just operate on is_true. Any more complicated filter would be a function (composed of other functions) returning a boolean value.

Another benefit is that function overloading would let us avoid some repetition in describing the filters.

Final example of something we'd eventually want to support: "return only rows where the sum of columns A and B is greater than the sum of A and B for any of the previous three rows". This is not a very complicated query, and requires a pretty general view of filters (and these sort of local max queries are not uncommon in my experience).

[dmos62]

As we discussed elsewhere, I support this idea. I think we'd get a lot out of more powerful ways to do filtering, sorting and grouping. But, I'm concerned that this might take a long time to implement. I'm leaning towards doing inexpressive filters now and working out the architecture for a more expressive system later.