join targeting for multiple, ambiguous base class joins

[Mike Bayer]

unfortunately the patch in #3366 does not resolve

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship
from sqlalchemy import Column, String, Integer
from sqlalchemy.schema import ForeignKey

Base = declarative_base()


class Object(Base):
    """ Object ORM """
    __tablename__ = 'object'

    type = Column(String(30))
    __mapper_args__ = {
        'polymorphic_identity': 'object',
        'polymorphic_on': type
    }

    id = Column(Integer, primary_key=True)
    name = Column(String(256))


class A(Object):
    __tablename__ = 'a'

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

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

    b_list = relationship(
        'B',
        secondary='a_b_association',
        backref='a_list'
    )


class B(Object):
    __tablename__ = 'b'

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

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


class ABAssociation(Base):
    __tablename__ = 'a_b_association'

    a_id = Column(Integer, ForeignKey('a.id'), primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'), primary_key=True)


class X(Base):
    __tablename__ = 'x'

    id = Column(Integer, primary_key=True)
    name = Column(String(30))

    obj_id = Column(Integer, ForeignKey('object.id'))
    obj = relationship('Object', backref='x_list')


s = Session()

# works
q = s.query(B).\
    join(B.a_list, 'x_list').filter(X.name == 'x1')

print q


# fails
q = s.query(B).\
    join(B.a_list, A.x_list).filter(X.name == 'x1')

print q

[Mike Bayer]

# fails (?!)
q = s.query(B).\
    join(B.a_list).join('x_list').filter(X.name == 'x1')

print q

[Mike Bayer]

OK, this might not be a bug or at least something we can change much. When we join('a', 'b'), the chaining of joins naturally uses "from_joinpoint" as it should. When we join('a').join('b') we don't, and at the moment when we join(B.a, A.b), we also don't. q.join(B.a).join(A.b, from_joinpoint=True) ensures it picks the right starting target. Issue here is that the joined-inh join has to use auto-aliasing and we don't document that this might be an issue.

[Mike Bayer]

we do expect to alias "A" here in the WHERE, why not in the join:

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 3e4ea24..98de42c 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1878,6 +1878,12 @@ class Query(object):

                 left_entity = onclause._parententity

+                # MARKMARK
+                alias = self._polymorphic_adapters.get(left_entity, None)
+                if alias:
+                    left_entity = alias.aliased_class
+                    onclause = getattr(left_entity, onclause.key)
+
                 prop = onclause.property
                 if not isinstance(onclause, attributes.QueryableAttribute):
                     onclause = prop

[Mike Bayer]

well here's a fully working patch but not sure what to do here. I think 1.1 is earliest on this:

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 8b3df08..d057143 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1881,6 +1881,15 @@ class Query(object):
                 if not isinstance(onclause, attributes.QueryableAttribute):
                     onclause = prop

+                # MARKMARK
+                alias = self._polymorphic_adapters.get(left_entity, None)
+                if isinstance(alias, ORMAdapter):
+                    alias = alias.aliased_class
+                    alias = inspect(alias)._entity_for_mapper(left_entity).entity
+                    left_entity = alias
+                    if isinstance(prop, properties.RelationshipProperty):
+                        onclause = getattr(left_entity, prop.key)
+
                 if not create_aliases:
                     # check for this path already present.
                     # don't render in that case.

[Mike Bayer]

starting to wrap up scope for 1.1, remaining behavioral changes being pushed out