python - sqlalchemy 中的多态自引用外键

Question

我正在尝试解决具有连接表继承的自引用表的问题，其中有一个外键链接继承关系，但也有一个类对其父实例有额外引用的情况。最好使用一个简化的示例:

B类继承自A类。B类通过B类中的外键通过id列链接到A类。B类还有一个列(a_id)，它引用了A类与继承无关。

from sqlalchemy import Column, Integer,ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from 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'))
    a = relationship('A', backref='b')

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

根据the documentation ，我将通过在要使用的关系中显式指定来解决表之间存在多个外键的情况。

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'))
    # I know the primaryjoin is no longer needed in SA >= 0.8
    a = relationship('A', backref='b', foreign_keys=[a_id], primaryjoin=a_id==A.id)

我认为问题是我似乎无法弄清楚如何对多态列 id 执行相同的操作，因为我没有明确定义该关系。

Answer 1

感谢 SA google 群组中的 Michael Bayer 提供此答案:

The "mutually dependent foreign keys" document doesn't really apply to this case. What happens here is that B(A) requires a join from B to A, and then B.a requires a different one. Even though the conventions here make it clear which foreign key constraint is which, the mapper still needs them to be explicitly spelled out, that's like 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'))
    a = relationship(
        'A',
        backref='b', primaryjoin=A.id == a_id, remote_side=A.id)