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 [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.
#! /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)
