Skip to content

Applying a loader option to a parent relationship when using with_polymorphic does not work properly #13210

@CaselIT

Description

@CaselIT

Asked claude to fuzz this and it did find a similar case, let me know if you prefer me to open another issue or if we should re-open this one. Seem very similar: it accesses the parent relationship via poly.Cls.parent_rel that's similar to what was fixed here poly.Cls.cls_rel

while strange I think it should work

"""
FINDING: Eager loading a base-class relationship through a subclass path on
a with_polymorphic entity does not work.
=============================================================================
Pattern:
    ap = with_polymorphic(Animal, "*", flat=True)
    selectinload(ap.Dog.meta)  # 'meta' defined on Animal base, accessed via Dog
This does NOT eagerly load. The attributes fall back to lazy loading.
Working alternatives:
    selectinload(ap.meta)      # Access the base-class relationship directly
Is this pattern documented?
    NOT EXPLICITLY. The with_polymorphic docs show:
    - ap.Engineer.engineer_info  (subclass-only attributes) ✓
    - ap.name                    (base-class attributes)    ✓
    - ap.Dog.meta (base-class rel through subclass namespace): NO examples
    However, Dog.meta IS a valid attribute (inherited from Animal), and
    ap.Dog is a valid namespace. So it's reasonable to expect ap.Dog.meta
    to work, since it resolves to the same underlying relationship.
    The argument for this being a bug: if ap.Dog.tag works (tag is Dog-only)
    and Dog.meta is a valid ORM attribute, then ap.Dog.meta should also work.
Tested on SQLAlchemy 2.1.0b2
"""

from sqlalchemy import String, ForeignKey, create_engine, select, event
from sqlalchemy.orm import (
    DeclarativeBase,
    mapped_column,
    Mapped,
    relationship,
    Session,
    with_polymorphic,
    joinedload,
    selectinload,
    subqueryload,
)
from typing import Optional


class Base(DeclarativeBase):
    pass


class Meta(Base):
    __tablename__ = "meta"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))


class Tag(Base):
    __tablename__ = "tag"
    id: Mapped[int] = mapped_column(primary_key=True)
    label: Mapped[str] = mapped_column(String(50))


class Animal(Base):
    __tablename__ = "animal"
    id: Mapped[int] = mapped_column(primary_key=True)
    type: Mapped[str] = mapped_column(String(50))
    name: Mapped[str] = mapped_column(String(50))
    # Relationship declared on BASE class
    meta_id: Mapped[Optional[int]] = mapped_column(ForeignKey("meta.id"))
    meta: Mapped[Optional["Meta"]] = relationship()
    __mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "animal"}


class Dog(Animal):
    __tablename__ = "dog"
    id: Mapped[int] = mapped_column(ForeignKey("animal.id"), primary_key=True)
    breed: Mapped[str] = mapped_column(String(50))
    # Relationship declared on SUBCLASS only
    tag_id: Mapped[Optional[int]] = mapped_column(ForeignKey("tag.id"))
    tag: Mapped[Optional["Tag"]] = relationship()
    __mapper_args__ = {"polymorphic_identity": "dog"}


class Cat(Animal):
    __tablename__ = "cat"
    id: Mapped[int] = mapped_column(ForeignKey("animal.id"), primary_key=True)
    color: Mapped[str] = mapped_column(String(50))
    __mapper_args__ = {"polymorphic_identity": "cat"}


# Setup
engine = create_engine("sqlite://", echo=False)
Base.metadata.create_all(engine)

with Session(engine) as s:
    m1 = Meta(id=1, name="alpha")
    m2 = Meta(id=2, name="beta")
    t1 = Tag(id=1, label="tag-a")
    t2 = Tag(id=2, label="tag-b")
    s.add_all([m1, m2, t1, t2])
    s.flush()
    s.add_all([
        Dog(id=1, name="Rex", breed="Lab", meta=m1, tag=t1),
        Dog(id=2, name="Buddy", breed="Poodle", meta=m2, tag=t2),
        Cat(id=3, name="Whiskers", color="orange", meta=m1),
    ])
    s.commit()

queries_during_access = []


def track(conn, clauseelement, multiparams, params, execution_options):
    queries_during_access.append(str(clauseelement))


event.listen(engine, "before_execute", track)


def test(label, stmt, accessor):
    queries_during_access.clear()
    with Session(engine) as s:
        objs = s.execute(stmt).unique().scalars().all()
        queries_during_access.clear()
        for obj in objs:
            accessor(obj)
        n = len(queries_during_access)
    status = "PASS (0 lazy)" if n == 0 else f"FAIL ({n} lazy loads)"
    print(f"  {label}: {status}")
    return n == 0


ap = with_polymorphic(Animal, "*", flat=True)

print("=" * 70)
print("REPRODUCTION: Base-class relationship via subclass polymorphic path")
print("=" * 70)
print()

print("--- Subclass-only relationship (ap.Dog.tag) - WORKS ---")
for name, fn in [
    ("joinedload", joinedload),
    ("selectinload", selectinload),
    ("subqueryload", subqueryload),
]:
    test(
        f"{name}(ap.Dog.tag)",
        select(ap).options(fn(ap.Dog.tag)),
        lambda o: o.tag if isinstance(o, Dog) else None,
    )

print()
print("--- Base-class relationship via subclass path (ap.Dog.meta) - FAILS ---")
for name, fn in [
    ("joinedload", joinedload),
    ("selectinload", selectinload),
    ("subqueryload", subqueryload),
]:
    test(
        f"{name}(ap.Dog.meta)",
        select(ap).options(fn(ap.Dog.meta)),
        lambda o: o.meta,
    )

print()
print("--- Base-class relationship via base path (ap.meta) - WORKS ---")
for name, fn in [
    ("joinedload", joinedload),
    ("selectinload", selectinload),
    ("subqueryload", subqueryload),
]:
    test(
        f"{name}(ap.meta)",
        select(ap).options(fn(ap.meta)),
        lambda o: o.meta,
    )

print()
print("--- Both: ap.Dog.tag + ap.Dog.meta (both via subclass) - meta FAILS ---")
for name, fn in [
    ("joinedload", joinedload),
    ("selectinload", selectinload),
    ("subqueryload", subqueryload),
]:
    test(
        f"{name}(ap.Dog.tag) + {name}(ap.Dog.meta)",
        select(ap).options(fn(ap.Dog.tag), fn(ap.Dog.meta)),
        lambda o: (
            (o.tag if isinstance(o, Dog) else None, o.meta)
        ),
    )

print()
print("--- Both: ap.Dog.tag + ap.meta (subclass + base) - WORKS ---")
for name, fn in [
    ("joinedload", joinedload),
    ("selectinload", selectinload),
    ("subqueryload", subqueryload),
]:
    test(
        f"{name}(ap.Dog.tag) + {name}(ap.meta)",
        select(ap).options(fn(ap.Dog.tag), fn(ap.meta)),
        lambda o: (
            (o.tag if isinstance(o, Dog) else None, o.meta)
        ),
    )

print()
print("=" * 70)
print("CONCLUSION:")
print("  ap.Dog.tag works (tag defined on Dog)")
print("  ap.Dog.meta FAILS (meta defined on Animal, inherited by Dog)")
print("  ap.meta works (direct base access)")
print()
print("  Dog.meta is valid in normal ORM usage, so ap.Dog.meta arguably")
print("  should also work. The workaround is to use ap.meta instead.")
print()
print("  NOT A DOCUMENTED PATTERN: The with_polymorphic docs do not show")
print("  accessing base-class relationships through the subclass namespace.")
print("  Use ap.meta (base-class direct) instead of ap.Dog.meta.")
print("=" * 70)

Originally posted by @CaselIT in #13193

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingloader optionsORM options like joinedload(), load_only(), these are complicated and have a lot of issuesorm

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions