Extending the filtering API

[dmos62]

I'm working on a backend record/row filtering PR that, amongst other things, seeks to programmatically expose what filtering options are available for what Mathesar type. To that end I used a predicate hierarchy that looks something like this:

class Greater(ReliesOnComparability, SingleParameter, Leaf, Predicate)

class Empty(NoParameter, Leaf, Predicate)

class In(MultiParameter, Leaf, Predicate)

class Not(SingleParameter, Branch, Predicate)

class And(MultiParameter, Branch, Predicate)

The used mixins hold almost all information necessary to construct a predicate on the frontend. These are exposed on the REST API like this:

> curl http://localhost:8000/api/v0/databases/1/types/

[
    {
        "identifier": "boolean",
        "name": "Boolean",
        "db_types": [
            "BOOLEAN"
        ],
        "filters": [
            {
                "name": "equal",
                "position": "leaf",
                "parameter_count": "single",
                "parameter_mathesar_type": "boolean"
            },
            {
                "name": "empty",
                "position": "leaf",
                "parameter_count": "none"
            },
            {
                "name": "in",
                "position": "leaf",
                "parameter_count": "multi",
                "parameter_mathesar_type": "boolean"
            },
            {
                "name": "not",
                "position": "branch",
                "parameter_count": "single"
            },
            {
                "name": "and",
                "position": "branch",
                "parameter_count": "multi"
            },
            {
                "name": "or",
                "position": "branch",
                "parameter_count": "multi"
            }
        ]
    },
    {
        "identifier": "datetime",
        "name": "Date & Time",
        "db_types": [
            "DATE",
            "TIME",
            "TIMESTAMP"
        ],
        "filters": [
            {
                "name": "equal",
                "position": "leaf",
                "parameter_count": "single",
                "parameter_mathesar_type": "datetime"
            },
            {
                "name": "greater",
                "position": "leaf",
                "parameter_count": "single",
                "parameter_mathesar_type": "datetime"
            },
            {
                "name": "greater_or_equal",
                "position": "leaf",
                "parameter_count": "single",
                "parameter_mathesar_type": "datetime"
            },
            {
                "name": "lesser",
                "position": "leaf",
                "parameter_count": "single",
                "parameter_mathesar_type": "datetime"
            },
            {
                "name": "lesser_or_equal",
                "position": "leaf",
                "parameter_count": "single",
                "parameter_mathesar_type": "datetime"
            },
            {
                "name": "empty",
                "position": "leaf",
                "parameter_count": "none"
            },
            {
                "name": "in",
                "position": "leaf",
                "parameter_count": "multi",
                "parameter_mathesar_type": "datetime"
            },
            {
                "name": "not",
                "position": "branch",
                "parameter_count": "single"
            },
            {
                "name": "and",
                "position": "branch",
                "parameter_count": "multi"
            },
            {
                "name": "or",
                "position": "branch",
                "parameter_count": "multi"
            }
        ]
    },
    ...

Notice that the endpoint knows not to say that you can use a predicate that relies on the type being comparable (such predicate is identified by mixin ReliesOnComparability), if that type is not comparable, because Mathesar types' comparability is being declared as well. So for example Boolean above does not list the predicate/filter greater as supported.

As a side note, I think we could consider extending our Mathesar type definitions so that they could hold interesting information (like whether they are comparable) similarly to how the predicate classes hold information in this PR (through composition and dataclass fields).

Above changes imply a new middlelayer for interpreting the filter specification passed by the frontend. Here I had to either mimick the SQLAlchemy format or create something else. I opted for a new format, because I couldn't see any significant incentive to maintain compatibility with SA. The formats' fundamental structure is similar enough to be an easy change on the frontend, in my opinion.

Here's what the new filter specification format looks like:

{ "and": [
    {"not":
        {"empty": {"column": "col1"}}},
    {"or": [
        {"equal": {"column": "col2", "parameter": 15}},
        {"in": {"column": "col3", "parameters": [1,2,3]}},
    ]}]}

It is more strict than the SA format. Only one root predicate: it does not support ambiguity about whether the implict branch [0] is an or or an and. It's careful with plurality: there's only leaf [0] parameters that can only be a single value (they use the singular key parameter) and those that can only have a list of values (that use the plural key parameters). Same with the branch predicates: not will always have one sub-predicate, while or and and will have a list.

A feature of the format I'm not sure about is to only use the not branch predicate to negate a predicate, i.e. not support not-equal, but use {"not": {"equal": {...}}} instead. I liked it initially because it reduces the size of the definition, but I now see that supporting "pre-negated leaves" is just a dozen lines of trivial code. The definition size might be a concern if we want to eventually support a lot of predicates, which might effectively double the definition size.

Could you provide feedback on these proposed changes?

[0] There's two types of predicates, branches and leafes. Branches (and, or, not) contain (are parametrized on) other predicates, while leaves (e.g. empty,equal,greater) are the remaining predicates.

[pavish]

@dmos62 I don't think we can use parameter_mathesar_type if the db types present within a single mathesar type fundamentally differ in the values they represent.

For eg., In datetime, the TIME db type only holds time related information. If we want to filter a column which is of db type TIME, we can only supply a TIME value as argument and cannot supply DATE or TIMESTAMP. Any client would need to know exactly what type would be an acceptable parameter. parameter_mathesar_type cannot be used here since it includes all three db types when only one is acceptable.

mathesar_type is used to represent a group of similar types. Having filter related information here makes sense since the applicable filters for a group are usually similar but the arguments passed for it are not. So, it would be better to remove parameter_mathesar_type. The following modal would be better in my opinion:

1. Each db_type has a mathesar_type as parameter. Each mathesar_type holds common information.
2. All operations happen through db_type only. mathesar_type is purely for grouping the types and for representational purposes.

This is the current modal I've been implementing on the frontend:
DB_TYPES

{
  ...,
  [DB_TYPES.Char]: {
    category: ABSTRACT_TYPE_CATEGORIES.Text,
    input: { type: 'string' },
    validation: {
      method: 'length',
      op: 'lte',
      value: 255,
    }
  },
  ...,
  [DB_TYPES.MATHESAR.Email]: {
    category: ABSTRACT_TYPE_CATEGORIES.Email,
    input: { type: 'string' },
    validation: {
      conjunction: 'and',
      terms: [
        {
          method: 'regex',
          op: 'apply',
          value: REGEXP.Email,
        },
        {
          method: 'length',
          op: 'lte',
          value: 256,
        },
      ],
    },
  },
  ...
}

ABSTRACT_TYPES

{
  ...,
  [ABSTRACT_TYPE_CATEGORIES.Text]: {
    filters: [
      {
        op: 'equals',
        parameters: 'single'
      },
      {
        op: 'is_empty',
        parameters: 'none'
      },
      {
        op: 'in',
        parameters: 'multiple'
      }
    ],
    groups: [
     ...,
    ],
  },
  ...,
}

[pavish]

Thinking again, using parameter_mathesar_type is a problem only with DateTime. Maybe we can consider splitting it up.

[dmos62]

@pavish we discussed this a bit on the discussion thread about whether to define filtering on Mathesar types or Postgres types. It was pointed out that if you can't operate on a Mathesar type without looking at the underlying Postgres type, it might be a good idea to break that Mathesar type up, like you said. We agreed that at this point we need to refine our definition of what a Mathesar type (aka abstract type) is.

[pavish]

@dmos62 This comment is for the proposed filter format.

1. I don't think we require the branch positions, since they can only ever be and, or and not. It seems unnecessary to provide them in the api response.
2. As mentioned in previous comment, the client cannot make use of parameter_mathesar_type. We can only rely on db_type for parameters.
3. The proposed filter specification looks okay to me, however this will require substantial UX changes:
   • The UX currently has a filter called not_empty. Such filters cannot be shown with the provided specification. The whole point of providing the list of filters from the api is so that the frontend need not have to know what to display. If the UX requires us to show not_empty, the frontend would have to be aware of what filters to show. This removes the whole purpose of having to send these from the api.
   • We need to update the UX to display NOT

[dmos62]

Regarding 1: I'm puzzled by the branching predicates (and, or, not). It's weird to include them with every type's description. The alternative seems to be to define a dedicated endpoint for filtering options, which might be a good idea. There you can say these are the common predicates, and these are type-dependent. I like this idea.

Regarding 2: addressed in above comments.

Regarding 3: I can include not- versions of predicates if that will speed things up on the UX and frontend.

[kgodey]

@dmos62 I'm with @pavish here, I'm not sure that we need to expose and, or, and not in the API at all. We can explain their usage in the API documentation.

You've received a bunch of feedback so far, I think it would be useful to integrate that and the duplicates filter and come up with a new example response that we can evaluate.