Filtering with joined tables

Question

I'm trying to get some query performance improved, but the generated query does not look the way I expect it to.

The results are retrieved using:

query = session.query(SomeModel).
        options(joinedload_all('foo.bar')).
        options(joinedload_all('foo.baz')).
        options(joinedload('quux.other'))

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'

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'

It works fine. The question is of course - how can I achieve this with sqlalchemy itself?

Answer 1

If you want to filter on joins, you use join():

session.query(SomeModel).join(SomeModel.foos).filter(Foo.something=='bar')

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 - 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