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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
