用联接表过滤 [英] Filtering with joined tables

问题描述

我正在尝试提高一些查询性能，但是生成的查询看起来并不像我期望的那样.

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:

http://docs.sqlalchemy.org/en/Latest/orm/tutorial.html#joined-load -"joinedload()不能替代join()"，以及:

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#joined-load - the note on "joinedload() is not a replacement for join()", as well as :

http://docs. sqlalchemy.org/en/latest/orm/loading.html#the-zen-of-eager-loading

这篇关于用联接表过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！