On Dec 18, 2012, at 3:50 PM, Gerald Thibault wrote:
> I've attached an example showing the issue. I have a class, Test, with a
> single polymorphic subclass, TestOne. I also have TestChild, which has a fk
> to Test, and a relation using that fk.
>
> When I execute:
>
> session.query(TestChild) \
> .options(joinedload(TestChild.parent)) \
> .get(1)
>
> I see this query:
>
> SELECT test_children.id AS test_children_id, test_children.test_id AS
> test_children_test_id, anon_1.test_id AS anon_1_test_id, anon_1.test_type AS
> anon_1_test_type, anon_1.test_name AS anon_1_test_name, anon_1.test1_id AS
> anon_1_test1_id, anon_1.test1_value1 AS anon_1_test1_value1
> FROM test_children LEFT OUTER JOIN (SELECT test.id AS test_id, test.type AS
> test_type, test.name AS test_name, test1.id AS test1_id, test1.value1 AS
> test1_value1
> FROM test LEFT OUTER JOIN test1 ON test.id = test1.id) AS anon_1 ON
> anon_1.test_id = test_children.test_id
> WHERE test_children.id = 1
>
> What I would like to see is this:
>
> SELECT <whatever>
> FROM test_children
> LEFT OUTER JOIN test on test_children.test_id = test.id
> LEFT OUTER JOIN test1 on test1.id = test.id
> WHERE test_children.id = 1
>
> How can I accomplish this? Also, the join type doesn't matter, they could be
> inner joins too, the important thing is getting rid of the subquery because
> it's completely locking up our database.
This is how joining from a left side to a polymorphic or inner-joined subclass
right side works - the target is always wrapped inside of a subquery, as
SQLAlchemy was written without ever resorting to the syntax of "x JOIN (y JOIN
z)", as this syntax was not supported by many backends when SQLA was created.
SQLite still does not support this syntax.
Hence when using ORM-generated queries you will always get "x [LEFT OUTER] JOIN
(select * FROM y [LEFT OUTER] JOIN z) AS anon_1".
Additionally, the aspect above that is "y [LEFT OUTER] JOIN z", which is the
"target" here, can be of many forms. In the default "polymorphic" case, it's a
LEFT OUTER JOIN. If the relationship referred to "Test1" directly, then it
would be an inner join, and parenthesization would be required given LEFT OUTER
join from the parent. If the Test/Test1 mapping were a concrete mapping, it
wouldn't be a JOIN, it would be a SELECT .. UNION SELECT ... [UNION...], and a
fully parenthesized subquery would be necessary there as well. There are
situations where the *left* side is itself a polymorphic outer join or similar,
and parenthesization is necessary.
So there's all kinds of combinations that can occur between <parent> <some kind
of join> <some kind of target selectable>, and SQLAlchemy's current status is
to treat them all exactly the same - the right side is always wrapped in a
subquery. It took years to get this right, but the end result is that
SQLAlchemy always returns the right result, in a truly enormous number of
circumstances. SQLA goes for "the right answer, always" first, performance
second.
So the issue of "unwrapping" this series when possible is an advanced
optimization issue. There are tickets to attempt this in various scenarios,
to "unwrap" when appropriate, to actually render the joins parenthesized
directly when SQLite isn't used, but these use cases are considerably
complicated. It means the current approach of one way that always works needs
to be broken down into an additional series of decisionmaking chains, where new
mistakes and inconsistencies can be introduced. It's really kind of the next
level for Query, and we're probably getting close to where we can go there, but
it's a long road. Query gets major new architectural changes with each major
release and we laid a ton of new groundwork in 0.8.
Right now, you can achieve an exact SQL statement using Table instances
directly. I wasn't sure if the polymorphic eager loading part would work here,
but it seems to:
test = Test.__table__
test1 = TestOne.__table__
query = session.query(TestChild).\
outerjoin(test, test.c.id == TestChild.test_id).\
outerjoin(test1, test1.c.id == test.c.id).\
options(contains_eager(TestChild.parent))
tc = query.filter(TestChild.id == 1).first()
YMMV with more complex scenarios, though.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
