[Sqlalchemy-tickets] Issue #3774: subqueryload across multiple of_type() fails to select correct "second to last" entity (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 3774: subqueryload across multiple of_type() fails to select correct "second to last" entity
https://bitbucket.org/zzzeek/sqlalchemy/issues/3774/subqueryload-across-multiple-of_type-fails

Michael Bayer:


related to #3773, we get another comma join in this:

```
#!python

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, sessionmaker, subqueryload,
                            with_polymorphic)
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.schema import ForeignKey

Base = declarative_base()


class A(Base):
    __tablename__ = 't_a'

    id = Column(Integer, primary_key=True)


class B(Base):
    __tablename__ = 't_b'

    type = Column(String(2))
    __mapper_args__ = {
        'polymorphic_identity': 'b',
        'polymorphic_on': type
    }

    id = Column(Integer, primary_key=True)

    # Relationship to A
    a_id = Column(Integer, ForeignKey('t_a.id'))
    a = relationship('A', backref='bs')


class B2(B):
    __tablename__ = 't_b2'

    __mapper_args__ = {
        'polymorphic_identity': 'b2',
    }

    id = Column(Integer, ForeignKey('t_b.id'), primary_key=True)



class C(Base):
    __tablename__ = 't_c'

    type = Column(String(2))
    __mapper_args__ = {
        'polymorphic_identity': 'c',
        'polymorphic_on': type
    }

    id = Column(Integer, primary_key=True)

    # Relationship to B
    b_id = Column(Integer, ForeignKey('t_b.id'))
    b = relationship('B', backref='cs')


class C2(C):
    __tablename__ = 't_c2'

    __mapper_args__ = {
        'polymorphic_identity': 'c2',
    }

    id = Column(Integer, ForeignKey('t_c.id'), primary_key=True)


class D(Base):
    __tablename__ = 't_d'

    id = Column(Integer, primary_key=True)

    # Relationship to B
    c_id = Column(Integer, ForeignKey('t_c.id'))
    c = relationship('C', backref='ds')

engine = create_engine('sqlite://', echo=True)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

for i in xrange(2):
    a = A()
    session.add(a)

    b = B2(a=a)
    session.add(b)

    c = C2(b=b)
    session.add(c)

    d = D(c=c)
    session.add(d)

session.commit()

b_b2 = with_polymorphic(B, [B2], flat=True)
c_c2 = with_polymorphic(C, [C2], flat=True)

# Broken -- the query on D has a cross join between
# (A join B) and (B join C join D).
r = session.query(
    A
).options(
    subqueryload(
        A.bs.of_type(b_b2)
    ).subqueryload(
        b_b2.cs.of_type(c_c2)
    ).subqueryload(
        c_c2.ds
    )
).all()


```

this would appear to be the fix:


```
#!diff

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 1d0058c..4920d5c 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -922,20 +922,11 @@ class SubqueryLoader(AbstractRelationshipLoader):
             # in the case of a one level eager load, this is the
             # leftmost "left_alias".
             parent_alias = left_alias
-        elif info.mapper.isa(self.parent):
-            # In the case of multiple levels, retrieve
-            # it from subq_path[-2]. This is the same as self.parent
-            # in the vast majority of cases, and [ticket:2014]
-            # illustrates a case where sub_path[-2] is a subclass
-            # of self.parent
-            parent_alias = orm_util.AliasedClass(
-                to_join[-1][0],
-                use_mapper_path=True)
+        elif info.is_aliased_class:
+            parent_alias = info.entity
         else:
-            # if of_type() were used leading to this relationship,
-            # self.parent is more specific than subq_path[-2]
             parent_alias = orm_util.AliasedClass(
-                self.parent,
+                info.entity,
                 use_mapper_path=True)
 
         local_cols = self.parent_property.local_columns

```