Displaying data from multiple collections in a single table

Hi everyone!

I’m trying to build a “summary” table that shows data per year from two different Supabase tables that each have a “year” property. In both tables there are multiple rows with the same year that I want to aggregate (sum and/or count). So each summary table row would look like this:

Year | Sum of property 1 from table 1 rows for this year | Count of table 2 rows for this year | Sum of property 1 from table 2 rows for this year

How should I go about implementing this in WeWeb?

Thanks!

GD

1 Like

Figured this out on my own actually. Bound the collection that is going to have the most distinct “year” values (if both/all your collections are equivalent in this respect, you can pick any), bound the year property to the first column, then used filterByKey with the item.year value to retrieve and rollup all items from the same collection and the other collection that have the same year value. It works great!

You can also use a view and join your tables.

I need to learn how to do that in Supabase but that sounds like another great option with probably better performance, thanks!

1 Like

You’re right. While the solution I initially came up with did work at a basic level to display the information I wanted, setting up a new view in Supabase and creating a new WeWeb collection from it is a much better solution overall since it works with pagination, etc. Thanks for the tip!

For anyone reading this in the future, here’s a super quick video by WeWeb’s Q explaining how to set this up: https://youtu.be/GOwod-j6OYw?si=wQTDR-YdpoaT4kGF

1 Like

You’re welcome!