我正在尝试提高一些查询性能,但是生成的查询看起来并不像我期望的那样.
I'm trying to get some query performance improved, but the generated query does not look the way I expect it to.
使用以下方法检索结果
query = session.query(SomeModel). options(joinedload_all('foo.bar')). options(joinedload_all('foo.baz')). options(joinedload('quux.other'))我想做的是对通过"first"连接的表进行过滤,但是这种方式不起作用:
What I want to do is filter on the table joined via 'first', but this way doesn't work:
query = query.filter(FooModel.address == '1.2.3.4')它会在查询中附加这样的子句:
It results in a clause like this attached to the query:
WHERE foos.address = '1.2.3.4'由于生成的联接附加了表foos_1和foos_2,因此没有以正确的方式进行过滤.如果我手动尝试该查询,但将过滤子句更改为:
Which doesn't do the filtering in a proper way, since the generated joins attach tables foos_1 and foos_2. If I try that query manually but change the filtering clause to:
WHERE foos_1.address = '1.2.3.4' AND foos_2.address = '1.2.3.4'工作正常.问题当然是-我如何使用sqlalchemy本身来实现这一目标?
It works fine. The question is of course - how can I achieve this with sqlalchemy itself?
推荐答案如果要过滤联接,请使用join():
If you want to filter on joins, you use join():
session.query(SomeModel).join(SomeModel.foos).filter(Foo.something=='bar')joinedload()和joinedload_all()仅用作一次加载相关集合的方法,不用于过滤/排序!.请阅读:
joinedload() and joinedload_all() are used only as a means to load related collections in one pass, not used for filtering/ordering!. Please read:
docs.sqlalchemy/en/Latest/orm/tutorial.html#joined-load -"joinedload()不能替代join()",以及:
docs.sqlalchemy/en/latest/orm/tutorial.html#joined-load - the note on "joinedload() is not a replacement for join()", as well as :
docs. sqlalchemy/en/latest/orm/loading.html#the-zen-of-eager-loading
更多推荐
用联接表过滤
发布评论