On 08/10/2016 06:18 PM, Douglas Russell wrote:
Hi,
I have two (well, more than two with more than two levels each, but this
is the simplest recreation of the problem) parallel joined-table
inheritance hierarchies
A -> A2
B -> B2
B.a is a relationship to A, backref in A.bs.
The problem arises when I want to query this whole structure back with
subqueries. The desired number of queries in this case would be twoish,
depending on how the polymorphic queries are done. At any rate, the
desire is to remove any lazy loading at all.
The problem lies in formulating a subqueryload which will do this. My
first thought was:
|
session.query(A2).options(subqueryload(A2.bs)).all()
|
The problem with this is that what I wanted was all the A2s and all the
B2s, but what I get is all the A2s, and all the Bs. When I access a
property of B2, it lazy loads that single record.
the use case here is accommodated by a per-load with_polymorphic:
from sqlalchemy.orm import with_polymorphic
r = session.query(A2).options(
subqueryload(A2.bs.of_type(with_polymorphic(B, [B2], flat=True)))
).all()
that's also your solution for joinedload(). the "flat=True" will reduce
the use of SELECT in favor of right-nested joins.
probably a link in the "loading objects" section should cross-reference
this, it's discussed in the mapper inheritance config section:
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#eager-loading-of-specific-or-polymorphic-subtypes
I think the best solution is something along the lines of using a
combination of subqueryload and joinedload (or maybe with_polymorphic?)
to say: Give me all the A2s with a subquery for all the B2s linked to
it. I can't figure out how to do this as the arguments to subqueryload
and joinedload are relationships which I don't currently have.
Minimal Test
Case: https://gist.github.com/dpwrussell/508e8b33fe92434eafe001ee806f355a
Is there a clever way to handle this?
The only thing I've done which works is to add a relationship between B
to B2:
|
b = relationship('B', backref='b2')
|
Then I can do:
|
r = session.query(A2).options(subqueryload(A2.bs).joinedload(B.b2)).all()
|
but it would be better if I didn't have to go and add these
relationships to the existing models as there are several levels,
repositories and (most hinderingly) jurisdictions in play in my real
project. It seems likely that there is a clever way as SQLAlchemy does
such a good job with the polymorphic queries usually.
Cheers,
Douglas
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
