Sqlalchemy, inheritance and relationships

I have a general User object for my site built as a base class using joined tables

class User(Base):
    __tablename__ = "auth_user"

    id = Column(Integer, primary_key = True)
    username = Column(String(100), nullable = False, unique = True, index = True)
    ...
    type = Column(String(1))
    __mapper_args__ = {'polymorphic_on' : type, "extension" : HashExtension()}

I then have a Staff object based on this class

class Staff(User):
    __tablename__ = "auth_staff"
    __mapper_args__ = {'polymorphic_identity' : 's'}    
    id = Column(Integer, ForeignKey('auth_user.id'), primary_key = True)

Now I also have a Candidate object, again derived from User

class Candidate(User):
    __tablename__ = "candidates_candidate"
    __mapper_args__ = {'polymorphic_identity' : 'c'}
    id = Column(Integer, ForeignKey('auth_user.id'), primary_key = True)
    ...
    staff_id = Column(Integer, ForeignKey("auth_user.id"), nullable = False)

    staff = relationship("Staff", backref = backref("candidates", order_by = id))

All's fine up to the end of the Candidate object. I want it to link back to a Staff object, but I'm getting errors about there not being a 'primaryjoin' and I am confused about how this relationship should link to the Staff object - should it link to the User object as Staff derives from it?....

Any suggestions would be gratefully received

~~~~~~~~~Update 3rd Feb~~~~~~~~~~~~~~

Revised Code - still throws errors about primaryjoin. If I add a primaryjoin it still chokes

#!/usr/bin/env python
from sqlalchemy import Column, Integer, String, ForeignKey, Boolean
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

class DomainBase(object):
    active = Column(Boolean, default = True, nullable = False)

    def __str__(self):
        return repr(self)

Base = declarative_base(cls = DomainBase)

class User(Base):
    __tablename__ = "auth_user"

    id = Column(Integer, primary_key = True)
    username = Column(String(100), nullable = False, unique = True, index = True)
    type = Column(String(1))
    __mapper_args__ = {'polymorphic_on' : type}
class Staff(User):
    __tablename__ = "auth_staff"
    __mapper_args__ = {'polymorphic_identity' : 's'}    
    id = Column(Integer, ForeignKey('auth_user.id'), primary_key = True)


class Candidate(User):
    __tablename__ = "candidates_candidate"
    __mapper_args__ = {'polymorphic_identity' : 'c'}
    id = 开发者_StackOverflow社区Column(Integer, ForeignKey('auth_user.id'), primary_key = True)
    staff_id = Column(Integer, ForeignKey("auth_staff.id"), nullable = False)

    staff = relationship("Staff", backref = backref("candidates", order_by = id))


engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

Session = sessionmaker(bind = engine, autocommit=True)
session = Session()

with session.begin():
    s = Staff(username = "DaveSmith")
    session.add_all([s])

---

Your example uses 2 foreign keys to base class table, so SQLAlchemy can't determine 1) what foreign key should be used for inheritance join, 2) what foreign key to use for staff relationship. You have to specify hints for both cases. Former requires 'inherit_condition' option in __mapper_args__ (see this answer for more info), later requires primaryjoin=(staff_id==User.id) argument to relationship.

But note, that your staff relationship refers to Staff class, while staff_id has foreign key refering to User's table. Although you mignt have some reson to so, it's not good in most cases. Changing staff_id definition to use ForeignKey("auth_staff.id") will solve your problem without other changes.