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 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. >>> >> >> -- >> 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. >> > > -- > 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. > -- 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.