Below, the mapping is A->m2m->B->o2m->C. We make two A objects, each
referencing the same B. The B then has some C objects on it.
We'd like the B to efficiently load its collection of C's via "subquery"
loading - so that any new B object will pull in its C list too. "subquery"
loading has the great advantage that all "cs" collections are loaded at once
for a given result set of Bs - but without needing to use a LEFT OUTER JOIN and
adding all of B's columns to each row.
With the change in place that states "an eager loader must fire off at all
times", a simple iteration of the two A objects and their .bs collection emits
an expensive subqueryload for the B.cs collection twice, when only one is
needed.
I also attached to ticket 2213 another rough idea that tries to improve upon
this, by checking for an additional "eager" level, which is kind of like your
original suggestion. But that really doesn't solve the problem - I add a "ds"
collection to C, then you still get a needless subqueryload for second load of
B objects.
The proposed behavioral change is less controversial for the joinedload case -
there, a lazyload of A.bs which specifies joinedload from B->C always emits the
JOIN in any case, so since we have the rows right there, using them to populate
whatever might be present seems reasonable.
For subqueryload, the use case is, we find a B.cs that isn't populated, we then
emit the "subqueryload", which will load the .cs of all B's that are in that
load.
I think the behavior is not as big a deal for joinedload, for subqueryload i
think we need to establish the definition of subqueryload as "fires off when an
unloaded attribute is encountered".
But, this does show that deciding what's "correct" might not be the same as
what is "the most useful". Leaving the behavior off for subqueryload means
we introduce an extra bit of behavioral difference between joinedload and
subqueryload, where the former will now "fill in" the blanks in all cases, the
latter still will not.
Yet another approach would be that the "descend into loaded collections"
behavior is explicit. This option would flip it on for both joined and
subqueryload. Or perhaps "descend into loaded" is enabled when the eager
chain begins from a user-initiated Query but not from a lazy load.
There are conflicting use cases here - one is "reduce the number of SQL
queries", the other is "fully load everything for the purposes of detachment".
The purpose of eagerloading is primarily the former. The proposed change in
many cases increases the amount of work needlessly if the "detached" use case
isn't required. Supporting both use cases in such a way that the rationale
either way is clear, we aren't needlessly complicating things, is very
challenging.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
a_to_b = Table('a_to_b', Base.metadata,
Column('a_id', Integer, ForeignKey('a.id')),
Column('b_id', Integer, ForeignKey('b.id'))
)
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship("B", secondary=a_to_b)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
cs = relationship("C", lazy="subquery")
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey('b.id'))
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
b = B(cs=[C(), C(), C()])
s.add_all([
A(bs=[b]),
A(bs=[b]),
])
s.commit()
s.close()
for a in s.query(A):
a.bs
On Jul 9, 2011, at 10:15 PM, Michael Bayer wrote:
>
> On Jul 9, 2011, at 9:50 PM, Michael Bayer wrote:
>
>>
>>
>> You have on your side that the proposed behavior does seem like its probably
>> more correct. I have a patch forthcoming that does the basic idea but I'd
>> need to think of some more tests, consider how / if it might be optional,
>> etc.
>
> okey doke I have something kind of doing it at
> http://www.sqlalchemy.org/trac/ticket/2213, I'd need to stare at this a while
> longer and add more tests, try some profiling, etc.
>
>
>
>
>
>>
>>
>>
>>
>>>
>>> Ben
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
