multiple joined table inheritance in class hierarchy

[Former user]

I tried the example for joined table inheritance, ie Employee, Manager, Engineer: this worked fine. However, if I try a similar case with an extra level, i get a problem when running the example Person>Employee>Engineer: the type field value does not propagate up in the class hierarchy, which causes the type field of the persons table to remain empty. This error is raised when flushing the session. Below the class diagram. The discriminator is twice the same field 'type', since (afaik) it is not possible to do it another way.

class Person(object): def init(self, social_security_id=None): self.social_security_id = social_security_id def repr(self): return self.class.name + " " + self.social_security_id

class Employee(Person): def init(self, name): self.name = name def repr(self): return self.class.name + " " + self.name

class Manager(Employee): def init(self, name, manager_data): self.name = name self.manager_data = manager_data def repr(self): return self.class.name + " " + self.name + " " + self.manager_data

class Engineer(Employee): def init(self, name, engineer_info): self.name = name self.engineer_info = engineer_info def repr(self): return self.class.name + " " + self.name + " " + self.engineer_info

persons = Table('persons', metadata, Column('person_id', Integer, primary_key=True), Column('social_security_id', String(50)), Column('type', String(30), nullable=False) )

employees = Table('employees', metadata, Column('employee_id', Integer, ForeignKey('persons.person_id'), primary_key=True), Column('name', String(50)), Column('type', String(30), nullable=False) )

engineers = Table('engineers', metadata, Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True), Column('engineer_info', String(50)), )

managers = Table('managers', metadata, Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True), Column('manager_data', String(50)), )

mapper(Person, persons, polymorphic_on=persons.c.type, polymorphic_identity='person') mapper(Employee, employees, inherits=Person, polymorphic_on=employees.c.type, polymorphic_identity='employee') mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer') mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

[Mike Bayer]

hi there -

the attached mapping is incorrect; the correct mapping has the "type" discriminator column base table and mapper only, for any depth of inheritance:

from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata = MetaData(engine)

class Person(object):
     def __init__(self, social_security_id=None):
         self.social_security_id = social_security_id
     def __repr__(self):
         return self.__class__.__name__ + " " + self.social_security_id

class Employee(Person):
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name

class Manager(Employee):
    def __init__(self, name, manager_data):
        self.name = name
        self.manager_data = manager_data
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name + " " +self.manager_data

class Engineer(Employee):
    def __init__(self, name, engineer_info):
        self.name = name
        self.engineer_info = engineer_info
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name + " " +self.engineer_info

persons = Table('persons', metadata,
    Column('person_id', Integer, primary_key=True),
    Column('social_security_id', String(50)),
    Column('type', String(30), nullable=False)
)

employees = Table('employees', metadata,
    Column('employee_id', Integer, ForeignKey('persons.person_id'),    primary_key=True),
    Column('name', String(50)),
)

engineers = Table('engineers', metadata,
    Column('employee_id', Integer, ForeignKey('employees.employee_id'),    primary_key=True),
    Column('engineer_info', String(50)),
)

managers = Table('managers', metadata,
    Column('employee_id', Integer, ForeignKey('employees.employee_id'),primary_key=True),
    Column('manager_data', String(50)),
)
metadata.create_all()

mapper(Person, persons, polymorphic_on=persons.c.type,polymorphic_identity='person')
mapper(Employee, employees, inherits=Person,polymorphic_identity='employee')
mapper(Engineer, engineers, inherits=Employee,polymorphic_identity='engineer')
mapper(Manager, managers, inherits=Employee,polymorphic_identity='manager')

sess = create_session()

p =Person(social_security_id='12345')
e =Employee(name='some employee')
e2 = Engineer(name='some engineer', engineer_info='foobar')
m1 = Manager(name='some manager', manager_data='manager data')

sess.save(p)
sess.save(e)
sess.save(e2)
sess.save(m1)

sess.flush()
sess.clear()

res = sess.query(Person).all()
print res
assert  repr(res) == repr([e, e2, m1](p,))
assert [for r in res](r.type) == ['employee', 'engineer', 'manager']('person',)

[Former user]

Hello, I've tried this, it indeed works, I got confused on where to have the discriminator field: in this case, you only need a discriminator field in the top level table

many thanks

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)