Getting data from a server in expensive in Retool. RDMSs like SQL Server and Postgres especially so. The fewer queries you can get away with that hit your servers (SQL or otherwise) the better.
Here is a technique that can do that.
A common use case is to have a table and a sub table (invoices/line items) or a Listbox that populates a second on selection (Car Make/Car Model.) You would typically select the Car Make you are interested in and the app queries the car models database for all models that match that make. You do a server round trip every time you click a car model.
But if your parent table in not too long and you do not have too many sub items, why not download all makes and all models (and all trims levels for a third level.)
Here is an example app:
All of the data is coming from this query called qryCars:
select(
select id_car_make, name,
(select id_car_model, name,
(select id_car_trim, name, start_production_year, end_production_year
from car_trim
where car_trim.id_car_model=car_model.id_car_model
FOR JSON PATH, INCLUDE_NULL_VALUES) as car_trim
from car_model
where car_make.id_car_make = car_model.id_car_make
FOR JSON PATH, INCLUDE_NULL_VALUES) as model
from car_make as car_make
FOR JSON PATH, INCLUDE_NULL_VALUES) as json
The output of this query is a string which contains JSON. You need to convert the string into an object which the transformer handles for us. That extra select ( ) as json is a little trick to name the string field that is returned to make it easier for the transformer to read the object.
The secret sauce here is JSON PATH. Here is some documentation on that: https://www.sqlshack.com/sql-server-json-functions-a-bridge-between-nosql-and-relational-worlds/ and Format Nested JSON Output with PATH Mode - SQL Server | Microsoft Learn and Solve common issues with JSON in SQL Server - SQL Server | Microsoft Learn
Now we have all of the car makes with an array of models under each and an array of trims under each of those - our data is nice and nested and normalized!
[{
"id_car_make": 76,
"name": "Honda",
"model": [{
"id_car_model": 753,
"id_car_make": 76,
"name": "Accord",
"car_trim": [{
"id_car_trim": 17958,
"id_car_model": 753,
"name": "2.4 AT (180 h.p.)",
"start_production_year": 2012,
"end_production_year": null
}, {
"id_car_trim": 17959,
"id_car_model": 753,
"name": "2.4 CVT (185 h.p.)",
...
Without JSON PATH your table would look like this with a lot of duplicated data:
Now let's make use of this. Let's start with Tables and Subtables. Place three tables and call them tblMake, tblModel and tblTrim.
In tblMake set the Data property to {{qryCars.data}}. You can hide the model column (which you will see is a bunch of JSON.)
Now set the Data property of tblModel to {{tblMake.selectedRow.data.model}}. Hide the car_trim column. Set the Data property of tblTrim to {{ tblModel.selectedRow.data.car_trim }}
You now have a working 3 table linkage which is pretty snappy. Each subtable is just reading the JSON in the parent table's selected row.
If you want to do the same thing but with Listboxes or Selects, it is very similar but a few more settings are needed.
Add three listboxes and call them lbMake, lbModel and lbTrim.
Set the properties of lbMake like so:
Setup lbModel like this:
This might take a little explanation. lbMake.selectedItem is comparable to a table's selectedRow and will include the entire object, including the model array which is used to populate the child listbox.
lbTrim works the same way, just one level deeper taking its data from lbModel:
Selects would be setup the exact same way.
If you were letting the user edit the data, you only have to trigger the on query to get everything updated. And if your users are doing a lot of edits to the one of the sub-sub table, you will need to refetch the entire JSON object after each change which is a downside.
I hope this helps you speed up your apps. If anyone wants the source tables and the app's json file just let me know.
