How to populate all items in a dataset with aggregated (Sum) data from another dataset?

Ask the community
, ,
1

I have a Vendors Dataset. My vendors earn money and those transactions are stored in a Transactions Dataset.

I would like to add a Sum of Payments column in my Vendors Dataset. Meaning I should probably aggregate the Transactions by Vendor ID.

How can I do this in a simple code.

I don’t even want to put code here as I don’t know what to start with. Is it a Transactions Aggregate grouped by “vendor ID” or do I start with a Vendors Query and inside that I have a Transactions Aggregate?

I’m completely blocked as to how to logically code this. I have basic code experience, and I think this should be relatively simple, but never done it before.

What is the logical order of events? And maybe an example of code would be really appreciated. Thanks.

2 
export function sumUp(vendorId){
return wixData.aggregate('Vendors')
.having(wixData.filter().eq('vendorId', vendorId))
.group('vendorId')
.sum('payments')
.run()
.then(res => {
    const items = res.items;
    let sum = 0;
    if(items.length > 0){
        sum = items[0].paymentsSum;
    }
    return sum;
})
}
3

Hi J.D. thank you for your response.

If I’m not mistaken, your last section sum= items[0].paymentsSum; will only populate the one item.

I am needing ALL items to be updated. What I am struggling with is how to aggregate and then loop through all items and update all items as efficiently as possible.

Eg.
Vendor 1 transactions: $10 + $10
Vendor 2 transactions: $20 + $30

I want my Vendors dataset to show in the Payments Sum column:
Vendor 1: $20
Vendor 2: $50

And for that calculation to be done all in one method.

4

@kscholiadis So remove this line:

.having(wixData.filter().eq('vendorId', vendorId))

and then the res.items will be an array of items that contains _id and paymentsSum.