在联接上扩展SQL查询的最佳实践

Question

我正在编写一个可与SQL一起使用的RESTAPI，并且经常发现自己处于与此类似的情况，在这种情况下，我需要通过查询表联接来返回对象列表，其中每个对象内部都有嵌套列表。

假设我在用户和组之间有一种多对多的关系。我有一个User表和一个Group表，它们之间还有一个连接表UserGroup。现在我想写一个REST端点，它返回用户列表，以及每个用户注册的组

[
{
"username": "test_user1",
<other attributes ...>
"groups": [
{
"group_id": 2,
<other attributes ...>
},
{
"group_id": 3,
<other attributes ...>
}
]
},
{
"username": "test_user2",
<other attributes ...>
"groups": [
{
"group_id": 1,
<other attributes ...>
},
{
"group_id": 2,
<other attributes ...>
}
]
},
etc ...

我可以想到两到三种查询SQL的方法：

1. 发出可变数量的SQL查询：查询用户列表，然后在每个用户上循环查询连接链接，以填充每个用户的组列表。SQL查询的数量随着返回的用户数量线性增加

示例(使用python flask_sqlalchemy/flask_restx(：

users = db.session.query(User).filter( ... )
for u in users:
groups = db.session.query(Group).join(UserGroup, UserGroup.group_id == Group.id) 
.filter(UserGroup.user.id == u.id)
retobj = api.marshal([{**u.__dict__, 'groups': groups} for u in users], my_model)
# Total number of queries: 1 + number of users in result

2. 发出恒定数量的SQL查询：这可以通过发出一个单一的SQL查询来完成，该查询执行用户列中可能有大量冗余数据的所有联接，或者更优选地，发出几个单独的SQL查询。例如，查询用户列表，然后查询GroupUsers上加入的Group表，然后在服务器代码中手动对组进行分组

示例代码：

from collections import defaultdict
users = db.session.query(User).filter( ... )
uids = [u.id for u in users]
groups = db.session.query(User.user_id, Group).join(UserGroup, UserGroup.group_id == Group.id) 
.filter(UserGroup.user_id._in(uids))
aggregate = defaultdict(list)
for g in groups:
aggregate[g.user_id].append(g[1].__dict__)
retobj = api.marshal([{**u.__dict__, 'groups': aggregate[u.id]} for u in users], my_model)
# Total number of queries: 2

3. 第三种方法用处有限，它是使用string_agg或类似的方法来强制SQL将一个分组连接到一个字符串列中，然后将该字符串解压缩到服务器端的列表中，例如，如果我只想要组号，我可以使用string_agg和group_by返回"1，2"；在对User表的一个查询中。但只有当您不需要复杂的对象时，这才有用

我被第二种方法所吸引，因为我觉得它更高效、更可扩展，因为SQL查询的数量(我认为这是主要的瓶颈，没有特别好的理由(是恒定的，但服务器端需要做更多的工作才能将所有组过滤到每个用户中。但我认为使用SQL的部分目的是利用它的高效排序/筛选，这样你就不必自己做了。

因此，我的问题是，我认为以牺牲更多的服务器端处理和开发时间为代价来保持SQL查询的数量不变是个好主意，这是对的吗？试图减少不必要的SQL查询的数量是在浪费时间吗？当API进行大规模测试时，如果我不这样做，我会后悔吗？有没有更好的方法来解决我不知道的这个问题？

Answer 1

使用joinedload选项，只需一个查询即可加载所有数据：

q = (
session.query(User)
.options(db.joinedload(User.groups))
.order_by(User.id)
)
users = q.all()
for user in users:
print(user.name)
for ug in user.groups:
print("  ", ug.name)

当您运行上面的查询时，所有组都已经使用类似于下面的查询从数据库中加载：

SELECT "user".id,
"user".name,
group_1.id,
group_1.name
FROM   "user"
LEFT OUTER JOIN (user_group AS user_group_1
JOIN "group" AS group_1 ON group_1.id = user_group_1.group_id)
ON  "user".id = user_group_1.user_id

现在，您只需要使用适当的模式序列化结果。