python - Polymorphic self referential foreign key in sqlalchemy -

i trying resolve problem self-referential table joined table inheritance there foreign key linking inheritance relationships, case class has additional reference instance of parent. best go simplified example:

class b inherits class a. class b linked class id column through foreign key in class b. class b has column (a_id) references class has nothing inheritance.

from sqlalchemy import column, integer,foreignkey, create_engine sqlalchemy.ext.declarative import declarative_base sqlalchemy.orm import sessionmaker, relationship, backref  base = declarative_base()   class a(base):     __tablename__ = 'a'     satype = column(string(50))     __mapper_args__ = {         'polymorphic_identity': 'a',         'polymorphic_on': satype     }      id = column(integer, primary_key=true)   class b(a):     __tablename__ = 'b'      id = column(integer, foreignkey('a.id'), primary_key=true)      __mapper_args__ = {         'polymorphic_identity': 'b'     }      a_id = column(integer, foreignkey('a.id'))     = relationship('a', backref='b')  engine = create_engine('sqlite:///:memory:', echo=true) base.metadata.create_all(engine) session = sessionmaker(bind=engine) session = session() 

as per the documentation, resolve case there multiple foreignkeys between tables explicitly specifying in relationship used.

class b(a):     __tablename__ = 'b'      id = column(integer, foreignkey('a.id'), primary_key=true)      __mapper_args__ = {         'polymorphic_identity': 'b'     }      a_id = column(integer, foreignkey('a.id'))     # know primaryjoin no longer needed in sa >= 0.8     = relationship('a', backref='b', foreign_keys=[a_id], primaryjoin=a_id==a.id) 

i think problem don't seem able figure out how same polymorphic column id not explicitly defining relationship.

thanks michael bayer in sa google groups answer:

the "mutually dependent foreign keys" document doesn't apply case. happens here b(a) requires join b a, , b.a requires different one. though conventions here make clear foreign key constraint which, mapper still needs them explicitly spelled out, that's this:

class b(a):     __tablename__ = 'b'      id = column(integer, foreignkey('a.id'), primary_key=true)      __mapper_args__ = {         'polymorphic_identity': 'b',         'inherit_condition': id == a.id      }      a_id = column(integer, foreignkey('a.id'))     = relationship(         'a',         backref='b', primaryjoin=a.id == a_id, remote_side=a.id)