Filtering, sorting and grouping of tables

[ghislaineguerin]

The design specs for #160 are ready for review. Please add any comments or suggestions you might have related to the design solution or any edge cases that need to be considered that could impact the design.

@kgodey @pavish @mathemancer your review confirmation is needed, even without feedback.

Refer to the review guidelines for additional advice on providing feedback https://wiki.mathesar.org/en/design/process/review-guidelines

Find the specs document here:
https://hackmd.io/@mathesar/HyPeStDcO

The review should be completed by end of this week (Friday, 11th June) so please make sure to allocate the necessary time.

[kgodey]

I added some comments to the HackMD document.

[mathemancer]

For a single filter, we're missing the concept of negation for some of the filter options. (E.g., there's no "does not end with"). Rather than go through piecemeal adding those options, I suggest taking out the "does not contain" negation, and somehow portraying (and giving the user the option to choose) the complement of a given filter.

[mathemancer]

For multiple filters, it's not clear to me whether the composition is intended to be AND or OR. It's also not clear to me which of those is the right choice (if we were to do only one). I suggest we should do both. The problem with doing both is that (especially combined with negation) we then need the concept of grouping and composing of filters on a level of sophistication that might become pretty busy in the side-panel interface. Example: I'm interested in pet-themed albums, but I think rats are gross as pets, so I want to make sure none of the albums I find include them (not really, just an example). I need to compose the filter (or equivalent):

(name contains 'dog' OR name contains 'cat') AND NOT name contains 'rat'

Note the parentheses. On the other hand, I might want to find pet-themed albums, but I don't want to be made sad by finding any albums where there could be a song about cats eating rats (because in this case I like rats). In that case, I need the filter:

name contains 'dog' OR (name contains 'cat' AND NOT name contains 'rat') OR (name contains 'rat' AND NOT name contains 'cat')

Note the parentheses.

I see how we might add negation and conjunction/disjunction features w.r.t. filtering later by extending the UI in the doc, but I'm not sure how it would be possible to add the concept of parentheses to an extension of the current UI. Even for the negation, conjunction, and disjunction concepts, I think the current UI might get pretty crowded.

[ghislaineguerin]

You make some good points re: the UI and how it might get crowded once we add filter logic. We could simplify the layout, make the panel wider and hide the controls once a filter is set to prevent that:

However, filter logic might add a lot of visual clutter if we attempt to represent all the relations and levels. A solution might be adding a filter logic option like:

In that case, we might need to add numbers to the filters or create a specific UI component that makes it easier to formulate the filter logic.

[mathemancer]

One option is to have a "simple filter" on the side panel, and an "advanced filter" in a separate element that can be brought up (users should be familiar with that flow). It still leaves the question (to me) of whether the simple filters should be composed with "and" or "or", and how to handle general negation. But, a lot of complexity is removed by only allowing one or the other of "and" or "or".

One option (Google's advanced search used to do this) is to treat commas within a given field as an "or", and treat separate fields as an "and". So, the first query above be:

(name contains 'dog' OR name contains 'cat') AND NOT name contains 'rat'

is input as:

name contains: dog, cat
name contains: NOT rat

Unfortunately, this puts a lot of burden on the user in the case of the second query. Translating that one into the appropriate form goes from

name contains 'dog' OR (name contains 'cat' AND NOT name contains 'rat') OR (name contains 'rat' AND NOT name contains 'cat')

to

name contains: dog, cat, rat
name contains: dog, NOT cat, NOT rat

The translation is completely unclear and "feels" illogical (though it has the exact same effect as the "human readable" SQL filter). Also, doing the actual translation required me to get out a pencil and paper, which is probably a bad sign.

The plus side is that the change is very minor to the UI (we'd just need to let them put multiple values in a field separated with commas), and can logically (as long as we allow the negations to be in the field with the values) handle any possible combination of conjunctions, disjunctions, and negations of filter operations.

[kgodey]

I don't think we can support "advanced" filtering (especially parenthetical expressions) using the current UI. If we want to support complex filtering, we need to use a query builder UI with autocomplete like @pavish used for his log management product. @pavish, maybe you could post some screenshots or a short video here?

Here are the options I see:
(1) Use a query builder interface instead of the current panel we have.
(2) Use our current panel but don't support grouping/composing queries. We can add advanced filtering via a query builder later.

Re: (1), I'm not sure that our default filtering interface should be a query builder, unless it's really intuitive to use. Non-technical users are probably not as familiar with complex queries as programmers, and we should make it easy for them. I think designing a really great query builder will take time and is probably not the best use of time right now. I think we should go with (2).

Airtable supports both And and Or queries, but they only let you select one of them which then applies to all the filters. You can see in the screenshots below that only the first "And"/"Or" has a dropdown next to it, and changing it changes the rest to the same thing. This seems like a good thing for us to do too.

[ghislaineguerin]

@kgodey as discussed, I think having an advanced filter as a future improvement makes sense.

[pavish]

I think we would need the option to group filters for the MVP, like Airtable does (in @kgodey 's screenshot). Based on the current design, we implicitly select the grouping as 'AND'. Giving an user additional option to change that seems like a good idea. I would consider this as a basic filter.

For the advanced query, I agree that we'll need a proper query builder (I will add some references and also a recording of the query builders I've worked with in the past, later). But I don't think we need it right away, it could be an improvement for later.

[pavish]

The idea is, for the basic filter we should be able to do this:

(column1 = value) AND (c2 = v2) AND (c3 = v3), and also this:
(column1 = value) OR (c2 = v2) OR (c3 = v3)

For the advanced query (which we can take up later, we will need to support:

((c1 = v1) AND ((c2 = v2) OR (c3 = v3))) OR (c4 = v4). This will lead to multi level nesting of conditions, and the UX for this needs to be thought out accordingly.

[mathemancer]

I think the slight extension for the basic filter is great. For the "advanced query", I think we should wait until we see some examples to get a feel for what's out there before deciding how to present them. There are a few forms that avoid nesting conditions, but which can be phrased so that they are logically equivalent to any (first order, predicate) logical statement, but translating into those forms would require some guidance for the user through a smart UI. The plus side is, once they get the hang of phrasing their queries in that way, it's actually a much simpler interface since they don't need to manage interactions between complex nested statements. Google Scholar's advanced search does this somewhat, and their old advanced search UI did it in a more general way.

[ghislaineguerin]

@pavish That was also the original intent for the filter design. We should be able to have "all" or "any" type of filtering, which in this case is resolved by the and/or options. I am updating the prototype to show a filter example that follows this pattern.

[kgodey]

Looks like we're all agreed on the filter design, let's finalize it.

[ghislaineguerin]

@kgodey @pavish @mathemancer
I have updated the prototype and document to include your comments and considerations for future updates. Please refer to the prototype for the latest version as screenshots might be out of date.

[pavish]

@ghislaineguerin I would like the interaction behaviour of automatically closing the left pane (with the tables), and the filter pane to be more detailed.

Also, what happens when a filter pane is open, and we move to a different tab. Does it stay open or do we open the left pane back?

I would prefer one quick synchronous meeting over this, so that I can understand this better.