adding other filter in Data explorer

[jeromeperez]

It could be interesting to add other filters in "Data explorer" such as upper, lower

[seancolsen]

Thanks for this suggestion @jeromeperez.

This suggestion is interesting because I can definitely understand the use case for needing upper and lower in some contexts, but I also don't see it as adding a "filter" per se... to me, this suggestion is more about adding functions, potentially in many different places throughout the Data Explorer. The "filters" we currently have are things like "equals" or "is lesser than". Those are comparisons. I can imagine how you might want to use upper or lower to perform a rudimentary form of case-insensitive filtering. That kind of filtering is structurally different from anything currently possible with the Data Explorer. As well, you might want to add a function like lower before summarizing on a column so that values are grouped together in a case insensitive manner. That's a structurally different form of summarizing than is currently possible within the Data Explorer. What's tricky here is that these sort of features can be a slippery slope into needing a great deal of complexity like composing multiple functions and operators into arbitrary expressions.

After Mathesar's beta release (later this year) we are planning to revisit the design of the Data Explorer, potentially changing some fundamental aspects of it. Use cases like yours are very much what is motivating us to rethink the Data Explorer design at a higher level, so understanding examples from real users is super helpful!

One of the ideas we are evaluating is to potentially offer some sort of "expert mode" within the data explorer where you could define queries using raw SQL. That would give you access to PostgreSQL string functions like upper and lower — and lots more! A further idea is to allow users to begin their queries using a form-based interface akin to the current Data Explorer UI (for ease of use) and then give them the capability of converting those queries to SQL for further customization manually.

I would be curious to hear your thoughts on incorporating SQL queries into the Data Explorer.

My personal opinion is that form-based query builders (like we have currently) are only suitable for simplistic queries — and that SQL is necessary for advanced queries beyond some basic filtering/sorting/summarizing. Admittedly, the line between "simplistic" and "advanced" is blurry. But as I see it there is some point at which the form-based approach just starts to devolve into a system which is both: too complex for basic needs — and too limiting for advanced needs. I would argue that the Data Explorer is actually already past that point. Within that blurry space between "simplistic" and "advanced", I also see some promise for AI tools to potentially help users create moderately advanced queries (but that's not a feature our team has yet discussed).

Not everyone on our team shares these opinions of mine though. Some maintainers are more optimistic about our ability to continue evolving the form-based Data Explorer to suit more sophisticated queries by continuing to build more elaborate UI for defining the query.

I would also be curious to hear your thoughts on how you might imagine specifying upper/lower within the Data Explorer using our current form-based approach to building queries? What if you wanted to filter on something like lower(substring("name" for 2)) = 'ab'? How would you define that? No pressure to come up with an actual design here, but any ideas/feedback/opinions would be useful for us at this stage. These are tricky problems to solve and we're open to considering many directions at this point.

[jeromeperez]

Hello.
There has been an error in translation, it's not upper or lower case but smaller or larger.
For example field1 > 12 or date1 <= 17 april 2024
Have you read my mind?
Because I was going to ask you to add a feature that would allow you to make SQL queries in Mathesar.

[seancolsen]

Oh, interesting. Sorry for the misunderstanding. The Data Explorer should already be capable of filtering on inequality comparisons. Here's an example:

For a number column, we have these options:

For other column types there are other comparison options. For example date columns have these comparisons possible:

Do the screenshots above represent the sort of thing you're trying to do?

[jeromeperez]

It's exactly what i mean. It's my time to be sorry; i hadn't seen these filters.