join on heavily with_polymorphic/base/subclass mismatch

Issue #2714 resolved
Michael Bayer
repo owner created an issue

this case needs to be further reduced and also may contain more than one individual issue, see the additional failures regarding aliased. fails in 0.7 also, so this is not as critical but still would like to research it. seems like the pattern "A join <joinedinh> join <joinedinh>" is somehow not working.

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session, aliased

Base = declarative_base()

class Email(Base):
    __tablename__ = 'Email'
    Id = Column(Integer, primary_key=True)

    # Many2One side of Address One2Many - backref Email.ItemEmail
    ItemEmail_Id = Column(Integer,
                          ForeignKey('Address.Id', use_alter=True, name="FK_Email_ItemEmail_Id"))

class Phone(Base):
    __tablename__ = 'Phone'
    Id = Column(Integer, primary_key=True)

    # Many2One side of Address One2Many - backref Phone.ItemPhone
    ItemPhone_Id = Column(Integer,
                          ForeignKey('Address.Id', use_alter=True, name="FK_Phone_ItemPhone_Id"))


class Address(Base):
    __tablename__ = 'Address'
    Id = Column(Integer, primary_key=True)

    AddressType = Column(String, nullable=False)

    # One2Many side of Phone Many2One - backref Phone.ItemPhone
    Phones = relationship("Phone", uselist=True, cascade="delete", backref="ItemPhone")

    # One2Many side of Email Many2One - backref Email.ItemEmail
    Emails = relationship("Email", uselist=True, cascade="delete", backref="ItemEmail")

    __mapper_args__ = {'polymorphic_on': AddressType,
                       'with_polymorphic': '*'
                       }

class PersonAddress(Address):
    __tablename__ = 'PersonAddress'
    Id = Column(Integer, ForeignKey('Address.Id'), primary_key=True)

    __mapper_args__ = {'polymorphic_identity': "person",
                        'inherit_condition': (Id == Address.Id)
                       }

    # ManyToOne side of Person OneTomany - backref PersonAddress.ItemPerson
    ItemPerson_Id = Column(Integer,
                           ForeignKey('Person.Id', use_alter=True, name="FK_PersonAddress_ItemPerson_Id"))



class Person(Base):
    __tablename__ = 'Person'
    Id = Column(Integer, primary_key=True)

    PersonType = Column(String, nullable=False)

    # One2Many side of PersonAddress Many2One - backref PersonAddress.ItemPerson
    Addresses = relationship("PersonAddress",
                             uselist=True, cascade="delete",
                             backref="ItemPerson")

    __mapper_args__ = {'polymorphic_on': PersonType,
                       'with_polymorphic': '*'
                       }

class UserPerson(Person):
    __tablename__ = 'UserPerson'
    Id = Column(Integer, ForeignKey('Person.Id'), primary_key=True)

    __mapper_args__ = {'polymorphic_identity': "user",
                        'inherit_condition': (Id == Person.Id)
                       }
    # One2One side of User One2One
    ItemUserPerson_Id = Column(Integer,
                               ForeignKey("User.Id", use_alter=True,
                                    name="FK_UserPerson_ItemUserPerson_Id"))
    ItemUserPerson = relationship("User",
                                  primaryjoin="User.Id==UserPerson.ItemUserPerson_Id",
                                  back_populates="Personal")


class User(Base):
    __tablename__ = 'User'
    Id = Column(Integer, primary_key=True)

    # One2One side of UserPerson One2One
    Personal = relationship("UserPerson",
                            primaryjoin="UserPerson.ItemUserPerson_Id==User.Id",
                            uselist=False,
                            cascade="delete",
                            back_populates="ItemUserPerson")


engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)

session = Session(engine)

# 1. users original q, but is in bad form
#session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
#    outerjoin(User.Personal, UserPerson.Addresses, PersonAddress.Phones,
    # PersonAddress.Emails)

# break them up, it breaks - only one of the PersonAddress.* joins
# can be accepted
# have not reduced for backrefs, with_polymrophic yet
#session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
#    outerjoin(User.Personal, UserPerson.Addresses).\
#    outerjoin(PersonAddress.Emails).\
#    outerjoin(PersonAddress.Phones).\
#    first()

# breaks
pa = aliased(PersonAddress)
session.query(User, UserPerson, pa).filter(User.Id == 1).\
    outerjoin(User.Personal).\
    outerjoin(pa, UserPerson.Addresses).\
    outerjoin(pa.Emails).\
    outerjoin(pa.Phones).\
    first()

# breaks? separate things wrong with aliased()? not sure
pa = aliased(PersonAddress)
session.query(User, UserPerson, pa).filter(User.Id == 1).\
    outerjoin(User.Personal).\
    outerjoin(pa, UserPerson.Addresses).\
    first()

# more breaks or not, haven't tested yet.

# 2 - OK - but I need PersonAddress.Emails
#session.query(User, UserPerson, PersonAddress).filter(User.Id == 1).\
#    outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones).first()

# 3 - error - just clutching at straws
#session.query(User, UserPerson, PersonAddress, Phone, Email).filter(User.Id == 1).\
#    outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

# 4 - OK - but why define Address
#session.query(User, UserPerson, PersonAddress, Address).filter(User.Id == 1).\
#    outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

# 5 - OK - but PersonAddress might have some fields not in Adddress
#session.query(User, UserPerson, Address).filter(User.Id == 1).\
#    outerjoin(User.Personal,UserPerson.Addresses,PersonAddress.Phones,PersonAddress.Emails).first()

Comments (6)

  1. Michael Bayer reporter

    OK luckily this has nothing to do with with_polymorphic or anything like that, and actually comes down to just one spot, as the way query.join() works out here, it just goes for "try to stick the right side anywhere we can", so it's more of a "find a spot" problem. but this is not the aliased issue yet. The join can be broken down like this:

        from sqlalchemy.orm.util import join as orm_join
        from sqlalchemy.orm.util import AliasedClass
    
        left = User.__table__
        right = AliasedClass(UserPerson)
    
        j1 = orm_join(left, right, User.Personal, join_to_left=True)
    
        right = AliasedClass(PersonAddress)
        j2 = orm_join(j1, right, UserPerson.Addresses, join_to_left=True)
    
        right = Email.__mapper__
    
        j3 = orm_join(j2, right, PersonAddress.Emails, join_to_left=True)
    
        # doesn't know how to find the right thing to alias the on clause
        # against
        right = Phone.__mapper__
        j4 = orm_join(j3, right, PersonAddress.Phones, join_to_left=True)
    
        print j4
    
  2. Michael Bayer reporter

    second issue:

        pa = aliased(PersonAddress)
    
        left = User
        right = AliasedClass(UserPerson)
    
        j1 = orm_join(left, right, User.Personal, join_to_left=True)
    
        right = pa
    
    
        j2 = orm_join(j1, right, UserPerson.Addresses, join_to_left=False)  # join_to_left is the problem
        print j2
    
  3. Michael Bayer reporter

    workarounds for now can be accomplished by using AliasedClass for all join targets and explicit ON clauses:

        up = aliased(UserPerson)
        pa = aliased(PersonAddress)
    
        session.query(User).\
            outerjoin(up, up.ItemUserPerson_Id == User.Id).\
            outerjoin(pa, pa.ItemPerson_Id == up.Id).\
            outerjoin(pa.Emails).\
            outerjoin(pa.Phones).\
            first()
    
  4. Log in to comment