SQLAlchemy嵌套列查询：如何聚合关联BB表的username字段

1. 核心实现：用SQL聚合函数聚合用户名 #

根据你使用的数据库类型，选择对应的聚合函数来将关联的username合并为列表：

针对PostgreSQL #

PostgreSQL原生支持array_agg函数，可直接返回数组类型结果：

from sqlalchemy import func

results = session.query(
    AA.id,
    AA.name,
    # 若需取关联BB的第一个name，用func.first；若需聚合所有name，替换为func.array_agg(BB.name)
    func.first(BB.name).label("bb_name"),
    func.array_agg(BB.username).label("usernames")
).join(AA.bbs).group_by(AA.id, AA.name).all()

针对MySQL #

MySQL使用group_concat返回逗号分隔的字符串，后续可转为列表：

from sqlalchemy import func

results = session.query(
    AA.id,
    AA.name,
    func.first(BB.name).label("bb_name"),
    func.group_concat(BB.username).label("usernames")
).join(AA.bbs).group_by(AA.id, AA.name).all()

# 将字符串转为列表
for row in results:
    row.usernames = row.usernames.split(',') if row.usernames else []

2. 原查询的问题分析 #

你之前的查询存在两个关键错误：

• 分组字段不完整：SQL标准要求SELECT中的非聚合字段必须出现在GROUP BY中，你仅分组了AA.name，但SELECT包含AA.id、BB.name等字段，导致结果不确定。
• 未使用聚合函数：没有对BB.username做聚合处理，因此只会返回每组中的单条记录，而非所有关联用户名的集合。

3. 关于BB.name的说明 #

从你的数据来看，AA1关联了两个不同的BB（bb1、bb2），但你期望结果中BB.name为bb1。如果是需要固定取第一个关联BB的name，用func.first(BB.name)；如果需要聚合所有关联BB的name，替换为func.array_agg(BB.name)即可。

4. 完整运行示例（PostgreSQL） #

from sqlalchemy import func

# 执行查询
results = session.query(
    AA.id,
    AA.name.label("aa_name"),
    func.first(BB.name).label("bb_name"),
    func.array_agg(BB.username).label("usernames")
).join(AA.bbs).group_by(AA.id, AA.name).all()

# 输出结果
for row in results:
    print(f"AA.id: {row.id}, AA.name: {row.aa_name}, BB.name: {row.bb_name}, BB.username: {row.usernames}")

输出结果：

AA.id: 1, AA.name: aa1, BB.name: bb1, BB.username: ['u1', 'u2']

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