Design for visual query builder ("Data Explorer")

[kgodey]

The Feature

Mathesar should support a visual query builder to allow users to construct more complex SQLSELECT queries than can be supported with filters or sorts in a single table or view.

Limitations:

• To keep the query builder simple, we will only be supporting a subset of SQL functionality. We plan to build a textual SQL editor for advanced users in the future.
• For now, we will only support building queries using tables. We can expand to queries that use views after the first version.

Use cases:

• Looking at data across multiple tables
• Looking at aggregate views of data in a single or multiple tables
• Performing complex queries on a single table that cannot be achieved with filters or sorts (e.g. finding duplicate rows)
• Looking at transformed or computed views of data
• Creating Views

Product Requirements

Since this is a large feature with many parts, this ticket is not broken down into individual scenarios. Instead, please see the Query Builder page of the product spec.

Design problems to solve

Representing different relationships between tables

Imagine a user building a query In the example schema below.

Scenario 1

1. They pick "Movie" as the base table and title as the first column in the View.
2. They pick full_name from the "Person" table as the next column

We need to represent the different ways that "Person" can be related to "Movie", which is:

• through Movie's director_id
• through Movie's first_credited_actor_id
• through the mapping table "Movie Cast Member"'s actor_id
• through the mapping table "Movie Crew Member"'s person_id

Scenario 2

1. They pick "Movie" as the base table and title as the first column in the View.
2. They pick runtime from the "Movie" table as the next column

We need to represent the different ways that we can retrieve the runtime of a "Movie" which is:

• the current record
• through Movie's prequel_id
• through Movie's sequel_id
• through Movie's sequel_id's prequel_id
• through Movie's sequel_id's sequel_id
• through Movie's sequel_id's prequel_id's prequel_id
• through Movie's sequel_id's prequel_id's sequel_id
• through Movie's sequel_id's sequel_id's prequel_id
• through Movie's sequel_id's sequel_id's sequel_id
• through the mapping table "Movie Recommendations"'s movie_id
• through the mapping table "Movie Recommendations"'s recommended_movie_id
• through the mapping table "Movie Recommendations"'s movie_id's sequel_id
• through the mapping table "Movie Recommendations"'s movie_id's prequel_id
• through the mapping table "Movie Recommendations"'s recommended_movie_id's sequel_id
• through the mapping table "Movie Recommendations"'s recommended_movie_id's prequel_id

and so on... (please note that we follow three levels deep of relationships)

Starting with a formula column vs. a direct column

When allowing the user to pick output columns, we need to allow users to either select a column from the available columns or select a formula. How should we represent this?

Column choices

In order to reduce choices for the user, we should make assumptions when they add a column but then show the user the assumptions we've made and allow them to change it. These include choices for:

• Source relationship
• Filters
• Formulas
• Aggregations

We should also allow users to delete columns easily, using patterns like multi-select.

Representing Formulas

We'll need to figure out how to represent formulas:

• How do we show the appropriate formulas given a column's data type?
• How do we allow users to enter variables into formulas?

Representing Filters

• How do we show the filters most appropriate to a column?
• How do we add/remove filters?

Representing Aggregations

• How do we show the aggregations most appropriate to a column?
• How do we handle the rest of the columns in a view when one column is aggregated?

How to set and show "base table"

Do we want users to manually select a base table or do we want to infer it from the first column they select (or allow both patterns?)

Asking them to select a table might help them think in a more object-oriented way and help them think of their view as centering around the object. We could add the most representative columns from a table to the view automatically since we will have already built this functionality for the record previews for foreign keys.

Where to put the query builder in the navigation

The query builder should be accessible from anywhere in Mathesar and should not be tied to a single table or view. This means we'll need to introduce a navigation pattern for it.

General design principles

• Reduce choices for the user, make assumptions but let the user change the assumptions
• Try and reduce modals, it's invasive and hard to have error/ confirmation steps within modals

Example schema

Please see the Example Schema page on the wiki

Additional context

• Views Product Spec