incorrect sql for with_polymorphic() along a relation path

(ProgrammingError) invalid reference to FROM-clause entry for table "principals" at character 737
HINT:  There is an entry for table "principals", but it cannot be referenced from this part of the query.

'SELECT anon_1.tickets_owner_id AS anon_1_tickets_owner_id, anon_1.tickets_id AS anon_1_tickets_id, anon_1.tickets_subject AS anon_1_tickets_subject, anon_1.principals_id AS anon_1_principals_id, anon_1.users_id AS anon_1_users_id, anon_1.principals_principal_type AS anon_1_principals_principal_type, anon_1.users_name AS anon_1_users_name,
   principals_1.id AS principals_1_id, principals_1.principal_type AS principals_1_principal_type
 FROM (SELECT tickets.owner_id AS tickets_owner_id, tickets.id AS tickets_id, tickets.subject AS tickets_subject, principals.id AS principals_id, users.id AS users_id, principals.principal_type AS principals_principal_type, users.name AS users_name
       FROM principals, tickets LEFT OUTER JOIN users ON principals.id = users.id
       LIMIT 1 OFFSET 0) AS anon_1
 LEFT OUTER JOIN principals AS principals_1 ON anon_1.tickets_owner_id = principals_1.id'



#!python
from sqlalchemy import *
from sqlalchemy.orm import *


metadata = MetaData()
Table('principals', metadata,
      Column('id', Integer, primary_key=True),
      Column('principal_type', String(16), nullable=False))

Table('users', metadata,
      Column('id', Integer, ForeignKey('principals.id'), primary_key=True),
      Column('name', String(200), nullable=False))

Table('groups', metadata,
      Column('id', Integer, ForeignKey('principals.id'), primary_key=True),
      Column('name', String(200), nullable=False),
      Column('description', String(255)))

Table('tickets', metadata,
      Column('id', Integer, primary_key=True),
      Column('owner_id', Integer, ForeignKey('principals.id'), nullable=False),
      Column('subject', String(200), default='[subject](no)'))


for name, value in metadata.tables.items():
    locals()[name](name) = value

class Principal(object):
    pass

class Group(Principal):
    pass

class User(Principal):
    pass

class Ticket(object):
    pass


mapper(Principal, principals,
       polymorphic_on=principals.c.principal_type)

mapper(User, users,
       inherits=Principal,
       polymorphic_identity='User')

mapper(Group, groups,
       inherits=Principal,
       polymorphic_identity='Group')

mapper(Ticket, tickets, properties={
    'owner': relation(Principal, backref='tickets',
                      primaryjoin=(tickets.c.owner_id ==
                                   principals.c.id))
    })

def data():
    principals.insert().execute(
        {'id': 1, 'principal_type': 'User'},
        {'id': 2, 'principal_type': 'Group'},
        {'id': 3, 'principal_type': 'Group'},
        {'id': 4, 'principal_type': 'User'})
    users.insert().execute(
        {'id': 1, 'name': 'user @ 1'},
        {'id': 4, 'name': 'user @ 4'})
    groups.insert().execute(
        {'id': 2, 'name': 'group @ 2'},
        {'id': 3, 'name': 'group @ 3'})
    tickets.insert().execute(
        {'id': 1, 'owner_id': 1, 'subject': 'Tix @ 1'},
        {'id': 2, 'owner_id': 2, 'subject': 'Tix @ 2'},
        {'id': 3, 'owner_id': 1, 'subject': 'Tix @ 3'},
        {'id': 4, 'owner_id': 1, 'subject': 'Tix @ 4'})


def test():
    s = create_session()
    s.query(Ticket).options(eagerload(Ticket.owner)).with_polymorphic(User).first()

if __name__ == '__main__':
    # passes on sqlite
    metadata.bind = 'postgres:///test'
    metadata.create_all()
    try:
        data()
        test()
    finally:
        metadata.drop_all()
Comments (5)
