join on heavily with_polymorphic/base/subclass mismatch

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)
