SQLAlchemy多表查询问题：无法获取Accounts关联Records的金额总和

问题 #

需要查询指定创建者的Accounts列表，同时附带每个Account对应的Records的amount字段总和。

当前使用的查询代码：

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 = 用户ID（创建者）
    )
).scalars().all()

Record表数据示例：

[
    {
      "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"
    }
]

期望结果：

[
  {
    "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
  }
]

实际执行后仅得到不含amount字段的Accounts数据，请问哪里操作有误？

解决方案 #

你的代码有三个核心问题：

1. 子查询未按账户分组：原subquery只计算了所有Record的amount总和，没有按account_id分组，无法得到每个账户对应的金额总和。
2. 主查询未包含金额字段：原查询只选择了Account实体，没有把子查询中的amount字段纳入结果集，所以最终数据里没有这个字段。
3. 关联条件错误：主查询的join直接关联了Record.account_id，但应该关联子查询中对应的账户ID字段。

修正后的代码如下：

# 子查询需要同时选择account_id并分组，确保每个账户对应自己的金额总和
subq = (
    select(
        Record.account_id,
        func.sum(Record.amount).label('total_amount')
    )
    .group_by(Record.account_id)
    .subquery()
)

# 主查询同时选择Account和子查询的total_amount，用outerjoin确保没有Record的Account也能被返回
result = await self.session.execute(
    select(Account, subq.c.total_amount)
    .outerjoin(subq, Account.uuid == subq.c.account_id)
    .filter(Account.creator_id == uuid)
)

# 将结果映射为包含amount字段的字典列表
accounts_with_amount = []
for account, total_amount in result:
    account_dict = {
        "uuid": account.uuid,
        "name": account.name,
        "creator_id": account.creator_id,
        "is_private": account.is_private,
        "amount": total_amount or 0  # 没有Record的账户默认金额为0
    }
    accounts_with_amount.append(account_dict)

关键改动说明 #

• 子查询新增Record.account_id作为关联字段，并通过group_by(Record.account_id)实现按账户分组求和。
• 主查询使用outerjoin替代join，避免漏掉没有关联Record的Account（如果不需要保留这类账户，可以换回join）。
• 同时选择Account和子查询的total_amount，最后将结果映射为包含amount字段的字典，匹配你期望的输出格式。

内容的提问来源于stack exchange，提问作者hardCodeMatter