Hi there!
I'm wondering if Retool's Table component has functionality to display grouped data (from say a array of objects input)? Or perhaps is there a way to group data on client side similar to the DataTables jQuery plugin?
Any help shedding light on this would be most appreciated!
Hi @maltase!
There's no in-built grouping feature on a Table component, but there's ways around it. For example, you can look into Transformers to transform the data based on a grouping, along with a Dropdown or Multiselect to choose from what attribute to group the data by.
Here's an example:
I see, thanks @retool_team !
When I copy this transformer, it does nothing to the table. I have an SQL query that I want to show multiple tables grouped in various ways without querying the db again. Hoping it is a bit faster.
The original query has 469 results. I tried grouping by one column name called "company_name". There are only 4 companies. But my results show 468 results.
Any help? I'm a bit of a javascript noob.....
@szabon not sure if you're still looking into this but...
In @retool_team's example the line const groupedData = data.map((d) => d) should be replaced by a function which groups and aggregates your data.
One way to group your data is to use the _.groupBy function from the built-in lodash library. Replacing line 6 with const groupedData = _.groupBy(data, groupBy) would set groupedData to be an object whose keys are the 4 companies in company_name and whose values are arrays of the objects that correspond to each company.
After this, your next step would still need to apply some aggregator function to the data for each company. What that looks like depends more on your specific use case. Combining _.mapValues with using reduce on each array can be super helpful here for general aggregation but reduce takes some getting used to.
To give a full example:
const data = formatDataAsArray({{ campaignQuery.data }});
const iteratee = 'company_name';
const groupedData = _.groupBy(data, iteratee);
const aggregator = (rows) => {/*define your aggregator here!*/};
const aggregatedData = _.mapValues(groupedData, aggregator);
return {company: Object.keys(aggregatedData), aggregate: Object.values(aggregatedData)};
As an alternative, if SQL is something you're more familiar with, you might want to try using Query JSON with SQL which lets you pass the data from your query and run additional SQL on it without having to query the db again.
@Kabirdas, can you give an example of what might go in the define your aggregator here! blank in your example?
Sure! One example would be to use something like _.sumBy to sum your data. I've attached an example you can import of what that might look like for some dummy data about foods:
What kind of aggregation are you looking to do?
aggregator_example.json