Should filtering options be defined per Postgres or Mathesar type?

[dmos62]

Part of the filtering functionality is to expose in the REST API the fitlering options available to each type. At the moment, our REST API only exposes metadata on the Mathesar types. However, @kgodey indicated that filtering options should be defined per Postgres type, which seems to contradict that architecture.

@mathemancer provided some input in chat, saying that filtering on Mathesar types should be a good abstraction:

I can't think of a place where I'd want a filter on a DB type that wouldn't be appropriate for all other DB types under a given Mathesar type. In fact, it seems that if we needed different filters there, it might be a signal that the Mathesar type should actually be multiple types.

I tend to agree, though I'm wondering if there's more to this.

[pavish]

I agree with @mathemancer's point as well. If the db types requires different filtering options, they should probably belong to a different Mathesar type.

I am wondering if there are any possible cases where grouping differs by db type for types within the same mathesar type. It would be better if we can bring in both filtering and grouping options within Mathesar types.

[kgodey]

Here's my reasoning for filtering and grouping being on DB types:

• Some Mathesar types that accept different filters e.g. "Date & Time" type which contains the TIMESTAMP, TIME, and DATE types. The filter parameters for each are not the same. e.g. you cannot pass a date to a TIME type or vice versa.
• We're building the DB module and API to support non-Mathesar UIs. People should be able to use our APIs without buying into our abstractions. If someone wants to build a UI that ignores the concept of Mathesar types entirely, they should be able to do so. We can't support that if filtering and grouping depends on Mathesar types.

[dmos62]

If we want the Mathesar type set to scale well then it's even more important to have a strong definition for what a Mathesar type is. The more different operations (like filtering) we can perform on a Mathesar type (without falling back to examining the underlying Postgres type) the stronger its definition. That's good for the user too, since it makes the interface simpler.

[kgodey]

Makes sense. We also have an ongoing discussion about potentially extending the Mathesar type system to View columns in #838.

I think a good next step might be for me to create a "Concepts" page for data types similar to the Views page and open that up for feedback so we can all be on the same page. That should help us both define the types better and give us a definition to rely on for further work.

[mathemancer]

I think the implementation of filtering should be in the DB layer, using SQL functions called from python. I wasn't really thinking of filters as being applied to Mathesar types implementation-wise. From this perspective, then the Mathesar type would provide a grouping of DB types where the operations would be consistent within a group of types. I see @kgodey 's point about this not quite working, though.

I wonder if we should eventually move towards the "Mathesar type" being "type group" instead? That way, the user could pick a type group, and get some automatic defaults (e.g., the database type and associated filters, sorting functions, aggregations, etc.). If they want, they could choose a different type in the group. It might also be possible (someday) for them to configure what type is the default for a given group. They could also configure some group-wide options in the DB (e.g., sorting).

@dmos62 if you create that page, please toss a link somewhere I might notice it. I agree that we should get these things hashed out pretty precisely at this juncture.

[dmos62]

@mathemancer that's quite exactly how I've been thinking about Mathesar types. I agree about filtering happening on Postgres. For the foreseeable future it should all be based on our fork of sqlalchemy-filters.

@kgodey I think some well targeted GitHub discussion threads would be a good compliment to starting that wiki page for types. I'll start one if a good prompt comes to mind.

[kgodey]

@dmos62 Sounds good.