SQLAlchemy多对多关联查询：如何返回包含用户名及对应位置ID列表的结果

你遇到的问题很典型——直接查询User.locations返回的是Location对象的集合，不是你需要的loc_id列表，而且原查询也没有帮你把结果整理成期望的字典格式。下面给你几种可行的解决方案，按需选择：

方法一：查询后手动处理（通用所有数据库） #

这种方法最直观，先查询用户及其关联的位置对象，再提取ID列表。为了避免N+1查询问题（每个用户单独查一次位置），记得用joinedload预加载关联数据：

from sqlalchemy.orm import joinedload

# 预加载用户的位置，减少数据库查询次数
users = db.query(User).options(joinedload(User.locations)).all()

# 整理成目标格式
result = [
    {
        "username": user.username,
        "locations": [loc.loc_id for loc in user.locations]
    }
    for user in users
]

如果需要包含没有关联任何位置的用户，这个方法也能自动处理（此时locations会是一个空列表）。

方法二：数据库层面聚合（性能更优，依赖数据库特性） #

如果数据量较大，推荐直接在数据库层面聚合出ID列表，减少Python端的处理开销。不同数据库的聚合函数略有区别：

针对PostgreSQL（使用array_agg） #

from sqlalchemy import func

# 关联用户和位置，聚合loc_id成数组
query_result = db.query(
    User.username,
    func.array_agg(Location.loc_id).label("locations")
).join(User.locations).group_by(User.user_id).all()

# 转成目标字典格式
result = [{"username": uname, "locations": loc_ids} for uname, loc_ids in query_result]

针对MySQL（使用group_concat） #

MySQL没有原生数组类型，需要用GROUP_CONCAT拼接成字符串后再拆分：

from sqlalchemy import func

query_result = db.query(
    User.username,
    func.group_concat(Location.loc_id).label("locations")
).join(User.locations).group_by(User.user_id).all()

# 把拼接的字符串转成整数列表
result = [
    {"username": uname, "locations": list(map(int, loc_ids.split(",")))}
    for uname, loc_ids in query_result
]

注意：保留无关联位置的用户

上面的join会过滤掉没有任何关联位置的用户，如果要保留这些用户，改用outerjoin，并处理空值：

# PostgreSQL示例
query_result = db.query(
    User.username,
    func.array_agg(Location.loc_id).filter(Location.loc_id.isnot(None)).label("locations")
).outerjoin(User.locations).group_by(User.user_id).all()

为什么你的原查询不行？ #

db.query(User.username, User.locations).all()返回的是元组列表，每个元组是(username, Location对象集合)，既不是你要的ID列表，也没有自动整理成嵌套字典格式。而且这种写法会触发N+1查询（先查所有用户，再逐个查每个用户的位置），性能较差。

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