Here's a list of pathological test cases that confuses the hell out of SA while trying to eager load more than 1 collections which are mapped to the same table using single table inheritance. In short, only joinedload*() appears to work out of all the eager loading methods. This pretty much means that supplying eager load options to a Query object doesn't mean you will always get back the same result.
Ideally, I'd like subqueryload*() and contains_eager() to work just like joinedload*() to prevent a situation where I have to waste bandwidth loading the same data over and over again or doing MxN queries. Is there anyway that I can do what I want without rewriting my complicated query in full SQL expression? Thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/u-PW089d3McJ. 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.
#! /usr/bin/env python from sqlalchemy import Column, Integer, Unicode, ForeignKey, Enum, Table, \ create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload, \ joinedload_all, contains_eager, subqueryload, subqueryload_all Base = declarative_base() metadata = Base.metadata class SearchOption(Base): __tablename__ = "searchoption" id = Column(Integer, autoincrement=True, primary_key=True) parent_id = Column(Integer, ForeignKey(id, onupdate="CASCADE", ondelete="CASCADE")) parent = relationship("SearchOption", uselist=False, remote_side=[id], backref=backref("children")) discriminator = Column("type", Enum("origin", "food", name="searchoptiontype")) __mapper_args__ = {"polymorphic_on": discriminator} displayname = Column(Unicode(64), nullable=False) class OriginOption(SearchOption): __mapper_args__ = {"polymorphic_identity": "origin"} class FoodOption(SearchOption): __mapper_args__ = {"polymorphic_identity": "food"} product_searchoption_table = Table("product_searchoption", metadata, Column("product_id", Integer, ForeignKey("product.id", onupdate="CASCADE", ondelete="CASCADE"), primary_key=True), Column("searchoption_id", Integer, ForeignKey("searchoption.id", onupdate="CASCADE", ondelete="CASCADE"), primary_key=True)) class Product(Base): __tablename__ = "product" id = Column(Integer, autoincrement=True, primary_key=True) origin = relationship(OriginOption, uselist=False, secondary=product_searchoption_table) foods = relationship(FoodOption, secondary=product_searchoption_table) if __name__ == "__main__": engine = create_engine("postgresql+psycopg2://tester:tester@localhost/test_eagerload", echo=True) Session = sessionmaker(engine) session = Session() metadata.create_all(engine) usa = OriginOption(displayname=u"usa") canada = OriginOption(displayname=u"canada") apple = FoodOption(displayname=u"apple") orange = FoodOption(displayname=u"orange") banana = FoodOption(displayname=u"banana") product1 = Product(origin=usa, foods=[apple]) product2 = Product(origin=canada, foods=[orange, banana]) product3 = Product() session.add(product1) session.add(product2) session.add(product3) session.commit() session.expunge_all() # If all the collections to eager load belong to a single table inheritance # mapping, there's no way to let SA know to optimize this it seems. p = session.query(Product)\ .options(subqueryload_all(Product.origin, Product.foods))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" # This is only eager loaded by the previous query assert [f.displayname for f in p.foods] == [u'orange', u'banana'] # This is still lazy loaded session.expunge_all() # Now all the collections are eagerly loaded, but extremely inefficient # because of all the MxN queries p = session.query(Product)\ .options(subqueryload(Product.origin), subqueryload(Product.foods))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" assert [f.displayname for f in p.foods] == [u'orange', u'banana'] session.expunge_all() # Default left outer join returns multiple rows for a 1-to-1 mapping Product.origin # Both are eager loaded, but still not optimized for single table inheritance p = session.query(Product)\ .options(joinedload_all(Product.origin, Product.foods))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" # The last row where the origin is NULL is set to the result, so this is None assert [f.displayname for f in p.foods] == [u'orange', u'banana'] session.expunge_all() # Only eager load Product.origin p = session.query(Product)\ .options(joinedload_all(Product.origin, Product.foods, innerjoin=True))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" assert [f.displayname for f in p.foods] == [u'orange', u'banana'] # Still lazily loaded session.expunge_all() # Works as expected, but clearly different from joinedload_all() p = session.query(Product)\ .options(joinedload(Product.origin), joinedload(Product.foods))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" assert [f.displayname for f in p.foods] == [u'orange', u'banana'] session.expunge_all() # Works as expected too, but different from joinedload_all() p = session.query(Product)\ .options(joinedload(Product.origin, innerjoin=True), joinedload(Product.foods, innerjoin=True))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" assert [f.displayname for f in p.foods] == [u'orange', u'banana'] session.expunge_all() # SA gets confused here and doesn't know how to reconstruct multiple collections # from a single table p = session.query(Product)\ .join(product_searchoption_table, Product.id == product_searchoption_table.c.product_id)\ .join(SearchOption, SearchOption.id == product_searchoption_table.c.searchoption_id)\ .options(contains_eager(Product.origin), contains_eager(Product.foods))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" assert [f.displayname for f in p.foods] == [u'orange', u'banana'] session.expunge_all() # I guess a contains_eager_all() is needed here p = session.query(Product)\ .join(product_searchoption_table, Product.id == product_searchoption_table.c.product_id)\ .join(SearchOption, SearchOption.id == product_searchoption_table.c.searchoption_id)\ .options(contains_eager(Product.origin, Product.foods))\ .filter(Product.id == 2).one() assert p.id == 2 assert p.origin.displayname == u"canada" assert [f.displayname for f in p.foods] == [u'orange', u'banana'] session.close() metadata.drop_all(engine)