Sqlalchemy如何按关联关系的实例数量进行过滤查询

方法1：直接对应原生SQL的标准写法 #

你提到的原生SQL中GROUP BY + HAVING count(*)的逻辑可以直接在SQLAlchemy中通过func.count和having方法实现，和原生逻辑完全对齐：

from sqlalchemy import func

# 示例：筛选孩子数量等于指定值的家庭
Family.query.join(Family.children)\
    .group_by(Family.id)\
    .having(func.count(Family.children.id) == amount_of_children)\
    .all()

如果需要实现大于、小于等过滤逻辑，只需要修改having中的比较符即可。

方法2：封装为属性实现你期望的简写语法 #

如果想要使用类似Table.relationship.length == 目标值的简洁写法，可以通过SQLAlchemy的混合属性（hybrid_property）将统计逻辑封装到模型类中：

from sqlalchemy import func, select
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship

class Family(Base):
    __tablename__ = "family"
    id = Column(Integer, primary_key=True)
    # 其他字段定义
    children = relationship("Child", back_populates="family")

    # 实例层面调用时返回实际关联的实例数量
    @hybrid_property
    def children_length(self):
        return len(self.children)
    
    # 查询层面调用时返回统计表达式
    @children_length.expression
    def children_length(cls):
        return select(func.count(Child.id))\
            .where(Child.family_id == cls.id)\
            .label("children_length")

封装完成后就可以直接使用你期望的过滤语法：

Family.query.filter(Family.children_length == amount_of_children).all()

注意事项 #

• 如果关联数据可能存在重复关联的情况，可以将func.count(Child.id)替换为func.count(func.distinct(Child.id))避免统计结果出错
• 大数量场景下建议给关联表的外键字段添加索引，提升统计查询的效率

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