grabbou
09/21/2022, 8:20 PMOlyno
09/21/2022, 8:41 PMgrabbou
09/21/2022, 8:51 PMGROUP BY
? I think I didn't use ORDER BY
anywhere throughout the code 😅sum
) to calculate total value of all records, I had to group them by somethingOlyno
09/21/2022, 9:14 PMgrabbou
09/21/2022, 9:22 PMsql
UPDATE public."Wallet" w
SET balance = sub.balance
FROM (
SELECT SUM(delta) AS balance
FROM public."Transaction" t
WHERE t."walletId" = new."walletId"
GROUP BY t."walletId"
) sub
WHERE w.id = new."walletId";
In other words:
When new transaction is created, it will be accessible via new
variable. Since transaction has walletId
(that's my schema model), I can get the Wallet that transaction was added to by comparing IDs:
sql
WHERE w.id = new."walletId";
Once I have that matching Wallet, next step is to update the balance.
How to do it? Well, we do a sub-query. Inside a sub-query, we SELECT all transactions for a given walletId
(new transaction + everything that happened in the past) and we perform aggregation to summarise your balance.
sql
SELECT SUM(delta) AS balance
FROM public."Transaction" t
WHERE t."walletId" = new."walletId"
GROUP BY t."walletId"
Then, we just substitute that in the original query after FROM
, so we can actually select these fields:
sql
UPDATE public."Wallet" w
SET balance = sub.balance
FROM (
SELECT SUM(delta) AS balance
FROM public."Transaction" t
WHERE t."walletId" = new."walletId"
GROUP BY t."walletId"
) sub
WHERE w.id = new."walletId";
And we're set.Olyno
09/21/2022, 9:28 PMgrabbou
09/21/2022, 9:29 PMOlyno
09/21/2022, 9:35 PMgrabbou
09/21/2022, 9:35 PMSimonP
09/22/2022, 8:27 PM