The classic & visual query builders were not new-user friendly. The user had to do a lot of guesswork to get the query right. Query Builder needs to be intelligent to help reduce the manual steps required to build a query for eg. inferring joins, groupings, aggregations, etc.
The new query builder is designed to improve the query-building experience. The idea was to reduce the number of steps by automatically guessing the joins and setting proper defaults wherever possible while still allowing the user to customize the query.
Major Improvements:
Guess joins based on the relationships between tables
Guess joins based on the columns selected from related tables
Guess group bys, aggregations, date formats, etc based on the columns selected
Guess chart type & chart options based on the result
The combination of these changes has reduced the number of steps required to build a query significantly.
The old queries will continue to work as before, in the classic query builder. The new query builder will be used for new queries. Although, the user can switch to the new query builder for old queries as well.
Transform section has been added which was previously hidden under the menu button.
Apply pivot transform to convert values from a row into columns. The results must have two grouping columns and one value column. Any other columns will be ignored. Only one pivot transform can be applied.
All queries are saved such that they can be used as tables in other queries. Reduces the step to save the query as a table.
Added a new Auto chart type which will try to guess the best chart type based on the result. This is useful when the user is not sure which chart type to use.
There's a way to unstore a query from the query builder menu.
The visual query block has been removed from the notebook interface. All the existing visual queries will be converted to native SQL queries.
Managing Table Relationships
Relationships are important for the query builder to infer joins between tables. There wasn't a way to add/edit/remove relationships between tables. The new RelationshipEditor allows the user to manage relationships between tables.
Note. Relationships are automatically imported if the data source is a Frappe app's database.
The table chart now supports filtering, sorting & pagination. This was made possible by using one of the best datatable libraries - tanstack-table. The table chart is now more powerful and can be used to show large datasets.
A new chart type PivotTable is added which allows the user to select rows, columns, values and to create a pivot table. The table is rendered using tanstack-table making filtering, sorting & pagination possible.
In an attempt to reduce the clutter in the Query list, a new list filter is added. By default, the user will see only the queries that they have created. However, the user can change the filter to see all the queries.
Adding a dashboard filter is now a little easier. The link column per chart is auto selected after selecting the column to filter on.
The table cell will show a Badge for each string if the column has a list of strings
Now you can get the query result of any query in a script by using the get_query_results method.
Performance improvements
Improved data source table & column import when syncing a table
Restore the sqlite file for demo data setup instead of importing the data from csv files
New Query Builder
The classic & visual query builders were not new-user friendly. The user had to do a lot of guesswork to get the query right. Query Builder needs to be intelligent to help reduce the manual steps required to build a query for eg. inferring joins, groupings, aggregations, etc.
The new query builder is designed to improve the query-building experience. The idea was to reduce the number of steps by automatically guessing the joins and setting proper defaults wherever possible while still allowing the user to customize the query.
Major Improvements:
The combination of these changes has reduced the number of steps required to build a query significantly.
https://github.com/frappe/insights/assets/25369014/cd4977c5-1120-4a79-9842-4a6a9b208944
Other Improvements:
The old queries will continue to work as before, in the classic query builder. The new query builder will be used for new queries. Although, the user can switch to the new query builder for old queries as well.
Transform section has been added which was previously hidden under the menu button.
Apply pivot transform to convert values from a row into columns. The results must have two grouping columns and one value column. Any other columns will be ignored. Only one pivot transform can be applied.
https://github.com/frappe/insights/assets/25369014/6fc8a200-6f44-4060-ab3d-b8ec5823876f
All queries are saved such that they can be used as tables in other queries. Reduces the step to save the query as a table.
Added a new
Autochart type which will try to guess the best chart type based on the result. This is useful when the user is not sure which chart type to use.There's a way to unstore a query from the query builder menu.
The visual query block has been removed from the notebook interface. All the existing visual queries will be converted to native SQL queries.
Managing Table Relationships
Relationships are important for the query builder to infer joins between tables. There wasn't a way to add/edit/remove relationships between tables. The new
RelationshipEditorallows the user to manage relationships between tables.Note. Relationships are automatically imported if the data source is a Frappe app's database.
https://github.com/frappe/insights/assets/25369014/95489d75-e8d2-4bf1-9171-aad7a268efdc
Filtering, Sorting & Pagination in Table Chart
The table chart now supports filtering, sorting & pagination. This was made possible by using one of the best datatable libraries -
tanstack-table. The table chart is now more powerful and can be used to show large datasets.https://github.com/frappe/insights/assets/25369014/6d9cf444-9611-4237-9ef9-036a6908669e
Pivot Table Chart
A new chart type
PivotTableis added which allows the user to select rows, columns, values and to create a pivot table. The table is rendered usingtanstack-tablemaking filtering, sorting & pagination possible.https://github.com/frappe/insights/assets/25369014/a63e3a4b-8ee3-4475-8859-0d7af170758d
Other Minor Improvements
In an attempt to reduce the clutter in the Query list, a new list filter is added. By default, the user will see only the queries that they have created. However, the user can change the filter to see all the queries.
Adding a dashboard filter is now a little easier. The link column per chart is auto selected after selecting the column to filter on.
The table cell will show a Badge for each string if the column has a list of strings
Now you can get the query result of any query in a script by using the
get_query_resultsmethod.Performance improvements
sqlitefile for demo data setup instead of importing the data from csv files