SQLAlchemy复合多态映射与深度表继承配置问题求解

SQLAlchemy 原生支持多层级的 Joined Table 多态继承，你只需要在中间类AbstractSurveyQuestion的映射参数中同时完成两层多态的配置即可，不需要额外引入插件或改写继承逻辑。

修正后的完整模型代码 #

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class AbstractQuestion(Base):
    __tablename__ = "abstract_question"
    id = Column(Integer, primary_key=True)
    questionTypeId = Column(
        Integer, ForeignKey("luQuestionTypes.id"), index=True, nullable=False
    )
    __mapper_args__ = {
        "polymorphic_identity": 0,
        "polymorphic_on": questionTypeId,
        "with_polymorphic": "*"
    }

class MultiChoiceQuestion(AbstractQuestion):
    __tablename__ = "multi_choice_question"
    id = Column(Integer, ForeignKey(AbstractQuestion.id), primary_key=True)
    __mapper_args__ = {"polymorphic_identity": 1}


class AbstractSurveyQuestion(AbstractQuestion):
    __tablename__ = "abstract_survey_question"
    id = Column(Integer, ForeignKey(AbstractQuestion.id), primary_key=True)
    surveyQuestionTypeId = Column(
        Integer, ForeignKey("luSurveyQuestionTypes.id"), index=True, nullable=False
    )

    __mapper_args__ = {
        # 第一层多态配置：作为AbstractQuestion子类的类型标识
        "polymorphic_identity": 2,
        # 第二层多态配置：声明自身作为调研题型分支的多态基类，指定鉴别字段
        "polymorphic_on": surveyQuestionTypeId,
        "with_polymorphic": "*"
    }


class RatingQuestion(AbstractSurveyQuestion):
    __tablename__ = "rating_question"
    id = Column(Integer, ForeignKey(AbstractSurveyQuestion.id), primary_key=True)
    # 配置第二层多态对应的类型标识，按你luSurveyQuestionTypes表的实际id赋值即可
    __mapper_args__ = {"polymorphic_identity": 1}

关键配置说明 #

• 两层多态的鉴别字段互不干扰：第一层用questionTypeId区分顶层题型大类（普通多选、调研类题等），第二层用surveyQuestionTypeId区分调研题下的细分题型（评分题、排序题等），SQLAlchemy会自动在对应层级做类型判断
• 你原始代码缺失了各模型的__tablename__配置，使用Joined Table继承模式时，每个非抽象映射类都必须指定独立表名，否则映射初始化会直接报错
• 两层都配置with_polymorphic: "*"后，查询顶层AbstractQuestion时会自动JOIN所有层级的子表，直接返回对应子类的实例，避免逐层查询的N+1问题
• 不要给AbstractSurveyQuestion加__abstract__ = True配置，它本身是第一层多态下的有效分支，对应数据库中独立的表记录，加抽象配置会导致第一层多态识别失败
• 如果你不需要直接实例化AbstractSurveyQuestion，可以把它的polymorphic_identity设为None，所有具体调研题子类配置对应surveyQuestionTypeId的标识值即可

效果验证 #

from sqlalchemy.orm import Session
with Session(engine) as session:
    # 插入普通多选题
    session.add(MultiChoiceQuestion())
    # 插入评分题，ORM会自动给questionTypeId赋值2、surveyQuestionTypeId赋值1
    session.add(RatingQuestion())
    session.commit()

    # 查询所有顶层题目，会自动返回匹配的子类实例
    for q in session.query(AbstractQuestion).all():
        print(type(q))
        # 输出结果：<class 'MultiChoiceQuestion'>、<class 'RatingQuestion'>

内容的提问来源于stack exchange，提问作者Moshe Vayner