I found a surprising difference between SQLAlchemy's joinedload, and subqueryload, specifically with how they handle with_polymorphic. When using subqueryload, I am not able to eagerly load a relationship on a subclass of the relationship included in the subqueryload, whereas joinedload seems to handle this just fine.
For example, say I have a User class that has-many Shape objects. A Rectangle is a shape, and say it has-a Widget related to it. When querying the User class, I'd like to be able to construct my query options so all of the user's rectangle's widgets will be eagerly fetched, and I'd like to query the shapes as a subquery (rather than a joined load) to reduce the amount of duplicated data in the returned result set. I can get it working by join loading the shapes relationship, but I can't get it working as a subquery. Any thoughts?
Example, in code:
import logging
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
backref,
joinedload,
relationship,
scoped_session,
sessionmaker,
subqueryload,
with_polymorphic)
from sqlalchemy import (
create_engine,
Column,
Integer,
ForeignKey,
MetaData,
String)
logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
class Shape(Base):
__tablename__ = "shapes"
__mapper_args__ = {"polymorphic_on": "type"}
id = Column(Integer, primary_key=True)
type = Column(String, nullable=False)
owner_id = Column(Integer, ForeignKey("users.id"))
owner = relationship("User", backref=backref("shapes"), cascade="all")
class Rectangle(Shape):
__tablename__ = "shapes_rectangles"
__mapper_args__ = {"polymorphic_identity": "RECT"}
id = Column(Integer, ForeignKey("shapes.id"), primary_key=True)
length = Column(Integer)
width = Column(Integer)
widget_id = Column(Integer, ForeignKey("widgets.id"))
widget = relationship("Widget", cascade="all")
class Widget(Base):
__tablename__ = "widgets"
id = Column(Integer, primary_key=True)
engine = create_engine("sqlite://", echo=True)
metadata = MetaData()
with engine.begin() as connection:
metadata = Base.metadata
metadata.create_all(connection)
session = scoped_session(sessionmaker(bind=engine))
user = User()
widget = Widget()
rectangle = Rectangle(owner=user, widget=widget, length=10, width=5)
session.add_all([user, widget, rectangle])
session.commit()
shapes_load = subqueryload(
User.shapes.of_type(
with_polymorphic(Shape, "*", aliased=True)))
session.remove()
query = session.query(User) .options(shapes_load.joinedload(Rectangle.widget))
logger.info("##########subqueryload begin")
user = query.one()
logger.info("##########accessing widget")
user.shapes[0].widget
logger.info("##########subqueryload end")
shapes_load = joinedload(
User.shapes.of_type(
with_polymorphic(Shape, "*", aliased=True)))
session.remove()
query = session.query(User).options(shapes_load.joinedload(Rectangle.widget))
logger.info("##########joinedload begin")
user = query.one()
logger.info("##########accessing widget")
user.shapes[0].widget
logger.info("##########joinedload end")
Relevant output:
INFO:__main__:##########subqueryload begin
2017-12-19 23:03:36,826 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2017-12-19 23:03:36,827 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id
FROM users
INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id
FROM users
2017-12-19 23:03:36,827 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2017-12-19 23:03:36,829 INFO sqlalchemy.engine.base.Engine SELECT anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.shapes_owner_id AS anon
_1_shapes_owner_id, anon_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_shapes_rectangles_length, anon_1.shapes_rectangles_wid
th AS anon_1_shapes_rectangles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id, anon_2.users_id AS anon_2_users_id
FROM (SELECT users.id AS users_id
FROM users) AS anon_2 JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON anon_2.users_id = anon_1.shapes_owner_id ORDER BY anon_2.users_id
INFO:sqlalchemy.engine.base.Engine:SELECT anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.shapes_owner_id AS anon_1_shapes_owner_id, anon
_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_shapes_rectangles_length, anon_1.shapes_rectangles_width AS anon_1_shapes_rect
angles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id, anon_2.users_id AS anon_2_users_id
FROM (SELECT users.id AS users_id
FROM users) AS anon_2 JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON anon_2.users_id = anon_1.shapes_owner_id ORDER BY anon_2.users_id
2017-12-19 23:03:36,829 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
INFO:__main__:##########accessing widget
2017-12-19 23:03:36,831 INFO sqlalchemy.engine.base.Engine SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.id = ?
INFO:sqlalchemy.engine.base.Engine:SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.id = ?
2017-12-19 23:03:36,831 INFO sqlalchemy.engine.base.Engine (1,)
INFO:sqlalchemy.engine.base.Engine:(1,)
INFO:__main__:##########subqueryload end
2017-12-19 23:03:36,832 INFO sqlalchemy.engine.base.Engine ROLLBACK
INFO:sqlalchemy.engine.base.Engine:ROLLBACK
INFO:__main__:##########joinedload begin
2017-12-19 23:03:36,837 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2017-12-19 23:03:36,838 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.s
hapes_owner_id AS anon_1_shapes_owner_id, widgets_1.id AS widgets_1_id, anon_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_sh
apes_rectangles_length, anon_1.shapes_rectangles_width AS anon_1_shapes_rectangles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id
FROM users LEFT OUTER JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON users.id = anon_1.shapes_owner_id LEFT OUTER JOIN widgets AS widgets_1 ON widget
s_1.id = anon_1.shapes_rectangles_widget_id
INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.shapes_owner_id AS anon_1
_shapes_owner_id, widgets_1.id AS widgets_1_id, anon_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_shapes_rectangles_length,
anon_1.shapes_rectangles_width AS anon_1_shapes_rectangles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id
FROM users LEFT OUTER JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON users.id = anon_1.shapes_owner_id LEFT OUTER JOIN widgets AS widgets_1 ON widget
s_1.id = anon_1.shapes_rectangles_widget_id
2017-12-19 23:03:36,839 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
INFO:__main__:##########accessing widget
INFO:__main__:##########joinedload end
I discovered a bug with how I was using with_polymorphic. This patch [1] fixes the subqueryload query, so it does not emit a query when accessing user.shapes[0].widget (which is the expected behavior).
In SQLAlchemy version 1.3, the original test case fails with an exception like:
ArgumentError: Attribute "Rectangle.widget" does not link from element "with_polymorphic(Shape, [])".
[1]
@@ -72,12 +72,13 @@ rectangle = Rectangle(owner=user, widget=widget, length=10, width=5)
session.add_all([user, widget, rectangle])
session.commit()
-shapes_load = subqueryload(
- User.shapes.of_type(
- with_polymorphic(Shape, "*", aliased=True)))
+polymorphic_shape = with_polymorphic(Shape, "*", aliased=True)
+shapes_load = subqueryload(User.shapes.of_type(polymorphic_shape))
session.remove()
-query = session.query(User) .options(shapes_load.joinedload(Rectangle.widget))
+query = session.query(User).options(
+ shapes_load.joinedload(polymorphic_shape.Rectangle.widget)
+)
logger.info("##########subqueryload begin")
user = query.one()
logger.info("##########accessing widget")
@@ -85,11 +86,13 @@ user.shapes[0].widget
logger.info("##########subqueryload end")
shapes_load = joinedload(
- User.shapes.of_type(
- with_polymorphic(Shape, "*", aliased=True)))
+ User.shapes.of_type(with_polymorphic(Shape, "*", aliased=True))
+)
session.remove()
-query = session.query(User).options(shapes_load.joinedload(Rectangle.widget))
+query = session.query(User).options(
+ shapes_load.joinedload(polymorphic_shape.Rectangle.widget)
+)
logger.info("##########joinedload begin")
user = query.one()
logger.info("##########accessing widget")