Polymorphic identities ignored in joins

[Former user]

Hi,

when you query a polymorphic entity, the resulting statement will contain a where-clause that filters the descriminator column for the entity's identity.

When you query an entity and join on a polymorphic entity, I would expect the join to also contain such a filter. It does not. (verified with 0.7.2 and 0.6.8)

Test case:

# -*- coding: utf-8 -*-
"""$Id$"""

from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

ADDRESS_TYPE_PERSON = 1
ADDRESS_TYPE_COMPANY = 2

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)


user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('fullname', String(50)),
            Column('password', String(12)))

address = Table('address', metadata,
            Column('id', Integer, primary_key=True),
            Column('user_id', Integer, ForeignKey('user.id')),
            Column('type', Integer),
            Column('email_address', String(50)))


class Address(Base):
    __table__ = address
    __mapper_args__ = {'polymorphic_on': address.c.type}


class PersonAddress(Address):
    __mapper_args__ = {'polymorphic_identity': ADDRESS_TYPE_PERSON}


class CompanyAddress(Address):
    __mapper_args__ = {'polymorphic_identity': ADDRESS_TYPE_COMPANY}


class User(Base):
    __table__ = user

    addresses = relationship(
        Address,
        cascade='all')


metadata.create_all(bind=engine)

session = Session()
user1 = User(name='john', fullname='John J. Rambo', password='Butterfly')
user1_personal_address = PersonAddress(email_address='[e-mail]')
user2 = User(name='jane', fullname='Jane . Rambo', password='ILoveMyHoneybun')
user2_company_address = CompanyAddress(email_address='[e-mail]')

user1.addresses.append(user1_personal_address)
session.add(user1)
user2.addresses.append(user2_company_address)
session.add(user2)
session.commit()


res = session.query(Address).all()
assert len(res) == 2
res = session.query(PersonAddress).all()
assert len(res) == 1
assert res[0](0).id == 1
res = session.query(CompanyAddress).all()
assert len(res) == 1
assert res[0](0).id == 2
res = session.query(User).join((CompanyAddress, CompanyAddress.user_id == User.id)).all()
assert len(res) == 1
assert res[0](0).id == 2

The latter query translates to

SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname, user.password AS user_password 
FROM user JOIN address ON address.user_id = user.id

but IMO should translate to

SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname, user.password AS user_password 
FROM user JOIN address ON address.user_id = user.id AND address.type=2

Let me know what you think.

Best regards, Thomas Wiebe

[Mike Bayer]

the issue here is that you're supplying join() with an explicit SQL expression to join on - "CompanyAddress.user_id==User.id" - SQLAlchemy will never re-interpret such an expression to mean something different.

if you'd like the criteria of the join to be composed by SQLAlchemy's object relational system, you'd join on the relationship:

res = session.query(User).join(User.addresses.of_type(CompanyAddress)).all()

SQL is:

SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname, user.password AS user_password 
FROM user JOIN address ON user.id = address.user_id AND address.type IN (?)

neat huh ? :)

[Former user]

Actually, it is. :-)

Thanks! Thomas