如何在SQLAlchemy关联表中定义带过滤的父子关系？

要实现你想要的son和daughter属性，你可以直接在Parent类中定义只读的关系（relationship），利用secondary参数关联到Association表，并通过primaryjoin和secondaryjoin添加类型过滤条件。这种方式会在SQL层面直接过滤数据，比Python层面的列表推导性能更好。

修改后的完整代码 #

from sqlalchemy import Table, Column, Integer, ForeignKey, String, and_
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")
    # 修正原代码的外键定义：需指向child_type表的主键列
    child_type = Column(Integer, ForeignKey('child_type.id'))

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")
    
    # 定义sons属性：过滤出类型为"son"的子项
    sons = relationship(
        "Child",
        secondary=Association.__table__,
        primaryjoin="Parent.id == Association.left_id",
        secondaryjoin=and_(
            Association.right_id == Child.id,
            Association.child_type == ChildType.id,
            ChildType.name == 'son'
        ),
        viewonly=True
    )
    
    # 定义daughters属性：过滤出类型为"daughter"的子项
    daughters = relationship(
        "Child",
        secondary=Association.__table__,
        primaryjoin="Parent.id == Association.left_id",
        secondaryjoin=and_(
            Association.right_id == Child.id,
            Association.child_type == ChildType.id,
            ChildType.name == 'daughter'
        ),
        viewonly=True
    )

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

class ChildType(Base):
    __tablename__ = 'child_type'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)  # 添加唯一约束确保类型名称不重复

# 测试代码
son_type = ChildType(name='son')
daughter_type = ChildType(name='daughter')
dad = Parent()
son = Child()
# 直接赋值类型id（若已将son_type加入session，也可直接赋值对象）
dad_son = Association(child_type=son_type.id)
dad_son.child = son
dad.children.append(dad_son)
daughter = Child()
dad_daughter = Association(child_type=daughter_type.id)
dad_daughter.child = daughter
dad.children.append(dad_daughter)

# 使用示例
# print(len(dad.sons))  # 输出1
# print(len(dad.daughters))  # 输出1

关键说明 #

1. 修正的小问题：原代码中Association的child_type字段外键未指定列名，正确写法应为ForeignKey('child_type.id')，否则SQLAlchemy无法识别关联目标。
2. relationship参数解释：
   • secondary=Association.__table__：指定Parent与Child的间接关联表为association。
   • primaryjoin：定义Parent与Association的关联逻辑（Parent.id匹配Association.left_id）。
   • secondaryjoin：组合三层条件，确保只筛选出对应类型的子项：Association关联Child、Association的类型关联ChildType、ChildType名称为目标值。
   • viewonly=True：标记该关系为只读，避免通过sons/daughters直接操作导致数据不一致（子项管理仍通过children属性操作Association对象）。

备选方案：Python层面过滤（适合小数据量） #

如果你的数据量不大，也可以用hybrid_property在内存中过滤，代码更简洁：

from sqlalchemy.ext.hybrid import hybrid_property

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")
    
    @hybrid_property
    def sons(self):
        return [assoc.child for assoc in self.children if assoc.child_type.name == 'son']
    
    @hybrid_property
    def daughters(self):
        return [assoc.child for assoc in self.children if assoc.child_type.name == 'daughter']

这种方式会先加载所有children关联对象再过滤，适合数据量小的场景；而前面的relationship方式会直接生成过滤后的SQL查询，性能更优。

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