My problem next: I can’t execute query, where need to get list of Accounts
with their Records
by account_id
as sum of Records
.
My query next:
subq = (
select(
func.sum(Record.amount).label('amount')
)
.filter(Record.account_id == Account.uuid)
.subquery()
)
accounts = (
await self.session.execute(
select(Account)
.join(subq, Account.uuid == Record.account_id)
.filter(Account.creator_id == uuid) # uuid = it's User ID (Creator)
)
).scalars().all()
Also, Record table looks like this:
[
{
"type": "income",
"amount": 2000,
"uuid": "cf876a3d-3395-4f5f-82b5-b496a66e107c"
},
{
"type": "income",
"amount": 5000,
"uuid": "fe25274d-111f-410c-a18c-04c73cbcc9db"
},
{
"type": "expense",
"amount": 3000,
"uuid": "7a151849-fb8e-47dc-96a0-50fef12233e8"
},
{
"type": "expense",
"amount": 750,
"uuid": "dd90988a-cd53-4125-8017-2e2ed05ec48c"
}
]
Desired result is:
[
{
"uuid": "38528eff-61d8-4210-8f9b-7739414dff4a",
"name": "newAccount",
"creator_id": "15fvh85j-012f-41bh-9km5-e22c8t78e321",
"is_private": false,
"amount": 25000
},
{
"uuid": "86256mdjv-40m7-96v3-4de1-1e946b356gs8",
"name": "personalAccount",
"creator_id": "867becf0-c86a-4551-af81-
"is_private": false,
"amount": 53500
}
]
But I got only the same without amount
.
What I do wrong?
2
Answers
I found the answer that solved my problem, this using
join
andmappings().all()
:I can see several issues with your queries:
1- A
groupby
clause is missing from your sub-query. You need to be selecting theaccount_id
and thesum(amount)
grouped byaccount_id
. This will give us the sum of amount for each account.2- In your main query, you are only selecting fields from
Account
3- The join condition in your main query is wrong and you are not specifying any keys from
subq
Here’s a possible fix: