On Jan 30, 2013, at 7:31 PM, Gerald Thibault wrote: > I have a user object, and an extended user object which inherits from the > base user object. I also have a test object, which has a FK to the extended > user object. When I try this: > > res = session.query(Test) \ > .options(joinedload('user')) \ > .all() > > I see this sql generated: > > SELECT test.id AS test_id, test.user_id AS test_user_id, anon_1.users_ext_id > AS anon_1_users_ext_id, anon_1.users_id AS anon_1_users_id > FROM test LEFT OUTER JOIN (SELECT users.id AS users_id, users_ext.id AS > users_ext_id > FROM users JOIN users_ext ON users.id = users_ext.id) AS anon_1 ON > anon_1.users_ext_id = test.user_id > > there are no limits or constraints on the subquery, so this takes a > nightmarish amount of time to resolve (hundreds of thousands of users in the > table), while the query i was expecting works instantly. The query I am > trying to achieve is: > > SELECT test.id AS test_id, test.user_id AS test_user_id, anon_1.users_ext_id > AS anon_1_users_ext_id, anon_1.users_id AS anon_1_users_id > FROM test > LEFT OUTER JOIN users_ext ON users_ext.id = test.user_id > JOIN users ON users_ext.id = users.id > > How can I generate the second query instead of the first? It seems like all > the parts are there, but put together in a terrible way that ensures it will > take the maximum amount of time to resolve.
The interesting thing is that your second query is incorrect. Because you're taking "test left outer join users_ext", then inner joining that with "users", you will not get any "test" rows for which a "user" row is not present, thus defeating the purpose of LEFT OUTER JOIN. The correct form is for the inner JOIN to be parenthesized, ideally it would be "FROM test LEFT OUTER JOIN (users_ext JOIN users <onclause>) <onclause>", but SQLAlchemy's ORM currently does not render the parenthesized JOIN like that - that form is still not supported by SQLite and years ago I think didn't work on earlier MySQL versions either, it instead does the parenthesized subquery, which is structurally different enough that we're not able to decide between alternate forms at SQL compilation time. You can render exactly that SQL, or any SQL, using the join() construct directly, such as: from sqlalchemy import join user_ext = UserExt.__table__ user = User.__table__ test = Test.__table__ j = join(user_ext, user) query(Test).select_from(outerjoin(test, j, test.c.user_id==user.c.id)).options(contains_eager(Test.user)) In my experience it's only MySQL's very poor query planner that actually is unable to optimize a subquery like that, is this MySQL ? > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > <inheritance2.py> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.