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
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’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!