Efficient Nested Tables in Retool: Expand-on-demand with per-row queries

[Paulo]

Today, we'll learn how to show nested details per parent row (e.g., customers → orders) without over-fetching or tying all nested tables to the same data.

When working with a large amount of data, fetching all rows for multiple tables can slow down our application on page load. For example, if we have ten thousand customers, and each customer has an average of a hundred orders, we'd be querying for over a million records at once.

Some of the main cons of querying once for all records are:

• High upfront cost in memory and transfer time.
• If you only look at a small subset (say, one customer’s orders), most of the data is wasted.
• Your app may feel slower initially.
• Can hit client/browser performance limits if you try to render large tables.

Instead, we can run a single query to retrieve our customers and only fetch their orders when the end user expands that row. Here is a screenshot of the result:

Note: We could also add server-side pagination to only retrieve a subset of customers. I'll skip that to keep it simple, but here is a link if you'd like to learn more.

---

Functionality

• Expand-on-demand: Only fetch child data when a row is expanded.
• Per-row isolation: Each nested table renders its own data slice.
• State management: Use an array keyed by the parent row index to store fetched child data. While an object could support more complex state management, this implementation relies exclusively on array indexing for reads and writes. As a result, all operations remain constant-time.

---

Implementation

Step 1: Create the queries to get Customers and Orders.

• The query to get customers is pretty straightforward: select * from table_name order by id.
• To create the query to get a customer's orders, we'll need to use additionalScope to pass the id of a customer when a row is expanded. If this is a new concept for you, check this topic. Here is an example:
  
  

Step 2: Create a state variable to cache child data

• Initialize an array sized to parentTable.data.length, filled with empty objects. Example:
  ordersByUser = new Array(getUsers.data.length).fill({})
  
  
  
  Note: we now have an array with 'n' empty objects, where 'n' is the number of customers we have.

Step 3: Add the necessary components to the Canvas

• One table component with expandable rows, and a table nested within the expandable row.
  
  

• The data source of the nested table should point to the element in the i index of the state variable. i is the index of the current item, in this case, the index of the selected row in the parent table (first row is 0, second row is 1, and so on). i and item within a table work similarly to how they work within a List View component.
  
  
  
  Note: On your end, you won't see the nested table populated at this point because the state variable we are using for state management hasn't been populated with orders yet.

Step 4: Fetch the orders for the selected customer:

• On the parent table, we need an "Expand row" event handler to trigger the getOrders query, sending the userId (customer's id) using additionalScope, but if we have already fetched it, we shouldn't need to fetch it again if the end-user collapses a row and expands it again.
• To avoid querying for the same data multiple times, we need an "Only run when" condition. Here is one example:
  
  
  
  Note: In the condition, we're checking if the state variable no longer has an empty object at that index to prevent querying multiple times. Even if there were no orders for that customer, we would have keys with empty arrays as values. For example,
  
  

Step 5: Populate the state variable when a row is expanded

• To populate the state variable, we need a success event handler on the getUserOrders query to replace the empty object at the correct index.
  
  
  
  Note: Here is our documentation on the setIn method.

---

What about edits?

• If the use case requires allowing edits within the nested tables, you'll need to update the state variable after updating the records at the database level.
• Achieving this will be different based on your resource. Some resources, like AWS Redshift, don't support returning the updated records (it only returns a count). If this is the case, you may need to run another SELECT to retrieve the updated rows and update the state variable.
• You may also need to handle the bulk update per nested table if you want to keep the built-in save action buttons:
  
  
• You can also remove the save actions from the table and add a single save button connected to custom code to run the bulk update.
• Because the implementation for edits may be different for each builder based on requirements, I won't dive into it now, but feel free to ask any questions!

[sacha]

Hey @Paulo

So let's say we don't use the ordersByUser state variable but instead we use getUserOrders onExpandRow query to populate the current expanded row nested table

All the nested tables will be populated? Even if not expanded?

I took the habit to allow only one row at a time to be expanded.
So does your use case improve performance by populating only expanded rows or does it allow to expand multiple rows with different datas?

Cheers!

[Paulo]

All the nested tables will be populated? Even if not expanded?

Hi @sacha, great question!
Retool will only create a slice of state for the tables that are visible. Therefore, only the nested tables that are rendered are populated. If you allow only one table to be visible at a time, Retool will populate just that one table, rather than all of the non-visible tables. This behavior is true regardless of whether the tables are connected to the same data source.

For example. Here is the state for the ordersTable (nested table) when three rows are expanded:

Now let's dive deeper.

If we only allow one row to be expanded at a time, the state for the nested table will still look the same as above, and the key that holds the data will only be updated when the row with that index is expanded again (rows with index 0, 1, or 4 in the screenshot).

So does your use case improve performance by populating only expanded rows or does it allow to expand multiple rows with different datas?

It allows expanding multiple rows with different data. It has improved performance compared to retrieving all records for the nested tables at once. However, your use case could also benefit from taking a similar approach, even if we only allow one row to be expanded at a time. This is because if the end-user returns to a previously opened row, we would no longer need to run the query to retrieve the associated data again, as it is stored in the state variable. Therefore, you'd save one query run for every time the end-user expands the same row.

[Anna123NW]

hey, thank you! Do you know the way, how to only display the action row button (e.g delete) if it’s nested table has data? Or it has not - but display row action button conditionally:) hence - how to reference child/parent relations for nested components.

[Jack_T]

Hi @Anna123NW,

How are you populating the nested table data?

In my example, I have a button(in the place if your delete example) and when the button is clicked it triggers a script action.

The nested component I have is text2, in your case I am guessing it would be something like table2 or table3 and this can be referenced inside the buttons run script.

From there you could use some JS logic like a ternary to check and see if and what data is inside the child component.