Views are “virtual tables”. Data is presented in rows and columns just like a table, but these rows and columns are calculated on the fly by pulling other data from wherever it is stored.
Views can involve combining data from multiple tables or other views, filtering, sorting, aggregating (grouping), or even creating entirely computed columns.
Under the hood, views are defined by a database (SQL) query.
Views in Mathesar¶
We expect that Tables will be used for entering simple data quickly and Views will be used for looking at data, creating reports, or editing inter-related data easily.
You should use Views when you’d like to:
- see data aggregated across multiple tables in one place.
- bookmark a subset of data in a table (e.g. filtered, sorted, or grouped data)
- summarize data (e.g. have a column that shows counts of records in a table)
- compute data (e.g. subtract the value of one column from another column and show the result in a new column)
Views vs. Tables¶
To think about the difference between Views and Tables, here’s an example. Imagine you want to track your movie watches in Mathesar. Questions you might be thinking about are:
- On what day of the week do I usually watch movies?
- When did I last see Brad Pitt in a movie?
- How many movies did I watch that were released in the 90s vs. the 2000s.
- What was the last foreign movie I watched?
In order to track the data necessary to answer these questions, here’s a possible structure for your tables:
|ID||Title||Release Year||Primary Language|
|13||Thelma & Louise||1991||English|
|22||Meet Joe Black||1998||English|
|33||Crouching Tiger, Hidden Dragon||2000||Mandarin|
|41||Crazy Rich Asians||2018||English|
Movie Actor Map¶
|ID||Movie ID||Actor ID|
The Role of Views¶
As is obvious, none of these tables answer your questions by themselves, even when filters, sorts, or aggregations are applied to an individual table. Also, when you watch a new movie, logging it would involve adding data to four separate tables. The job of views is to simplify that.
Imagine a view with this structure:
|ID||Title||Actors||Last Watched||Times Watched||Language||Release Year|
|13||Thelma & Louise||Brad Pitt, Geena Davis||2021-10-13||2||English||1991|
|22||Meet Joe Black||Brad Pitt||2021-10-01||1||English||1998|
|33||Crouching Tiger, Hidden Dragon||Michelle Yeoh, Zhang Ziyi||2021-09-23||1||Mandarin||2000|
|41||Crazy Rich Asians||Michelle Yeoh||2021-08-12||1||English||2018|
Once you have set up this view, you could filter and sort to get answers to your questions, or add new movie watches easily by adding all relevant data from one place.
In the future, we will offer alternate display modes for Views (e.g. calendar, map, kanban, etc.).