join targeting for multiple, ambiguous base class joins

Issue #3505 resolved
Michael Bayer
repo owner created an issue

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
# JOIN x ON object_1.id = x.obj_id
q = s.query(B).\
    join(B.a_list, 'x_list').filter(X.name == 'x1')

print(q)


# fails
# JOIN x ON object.id = x.obj_id
q = s.query(B).\
    join(B.a_list, A.x_list).filter(X.name == 'x1')

print(q)

Comments (13)

  1. Michael Bayer reporter

    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.

  2. Michael Bayer reporter

    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
    
  3. Michael Bayer reporter

    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.
    
  4. Michael Bayer reporter

    Look up adapter info for previous left side in chained query.join()

    Fixed issue where chaining multiple join elements inside of :meth:.Query.join might not correctly adapt to the previous left-hand side, when chaining joined inheritance classes that share the same base class.

    Change-Id: I4b846430b7362912dbebf50599ec15a1eb978fd4 Fixes: #3505

    → <<cset f683ddf16b34>>

  5. Log in to comment