By

文章目录

在使用SQLAlchemy的ORM做关联查询filter时出了一点问题，同时也暴露出对SQLAlchemy的DSL的一些漏洞。

现在要查询的Model是Submission，Submission有两个外键submitter_id和problem_id，分别关联User和Problem这两个Model。
然后查询时想要对Submission的两个外键ModelUser和Problem的字段做filter，但是不需要加载这两个外键Model。

于是我写成了这样

1
2
3
4
5
6
7
8
session.query(Submission).options(
    joinedload("submitter"), joinedload("problem")
).filter(User.username == "JZQT").all()


session.query(Submission).options(
    joinedload("submitter"), joinedload("problem")
).filter(Problem.remote_pid == "1000").all()

结果发现查询出来的Submission对象并不满足这些限制条件。
生成的SQL语句如下

1
2
3
4
5
6
7
8
9
10
11
SELECT {Submission, User, Problem 的所有字段名}
FROM {Submission, Problem 和 User 的数据表名}
LEFT OUTER JOIN {User数据表名} ON {User.id == Submission.submitter_id}
LEFT OUTER JOIN {Problem数据表名} ON {Problem.id == Submission.problem_id}
WHERE {User.username == "JZQT"}

SELECT {Submission, User, Problem 的所有字段名}
FROM {Submission, Problem 和 User 的数据表名}
LEFT OUTER JOIN {User数据表名} ON {User.id == Submission.submitter_id}
LEFT OUTER JOIN {Problem数据表名} ON {Problem.id == Submission.problem_id}
WHERE {Problem.remote_pid == "1000"}

很明显这样的SQL不符合我们“不需要加载这两个外键Model”这个要求，因为它SELECT了User和Problem。

实际上这样的SQL造成的效果是这样的

如果数据库中存在username等于"JZQT"的User对象，那么你会查询出所有的Submission。
同理，如果数据库中存在remote_pid为"1000"的Problem对象，会查询出所有的Submission。
如果不存在，也就是说User或Problem中没有满足where子句条件的对象，那么查询不出来Submission。

这样的SQL查询当然不符合我们的要求。

经过一段时间的琢磨，终于发现了一个符合要求的方法。

1
2
3
4
5
# 重点在join
# 实际上如果不需要加载外键的Model，仅仅是用来filter的话，options里面的joinedload是不需要的
session.query(Submission).join(Problem, User).filter(User.username == "JZQT").all()

session.query(Submission).join(Problem, User).filter(Problem.remote_pid == "1000").all()

它生成的SQL语句如下

1
2
3
4
5
6
7
8
9
10
11
SELECT {Submission 的所有字段名}
FROM {Submission数据表名}
JOIN {Problem的数据表名} ON {Problem.id == Submission.problem_id}
JOIN {User的数据表名} ON {User.id == Submission.submitter_id}
WHERE {User.username == "JZQT"}

SELECT {Submission 的所有字段名}
FROM {Submission数据表名}
JOIN {Problem的数据表名} ON {Problem.id == Submission.problem_id}
JOIN {User的数据表名} ON {User.id == Submission.submitter_id}
WHERE {Problem.remote_pid == "1000"}

暂时先用这个了。

从以上SQL也可以看出来，joinedload默认是左外连接LEFT OUTER JOIN的。
另外，对与SQLAlchemy的联表问题，我理解的不够，有漏洞。
数据库SQL其实也不熟悉。

看来还是要好好学习一下才行。

文章目录