python - 如何在此查询中添加 if 条件？

Question

我正在使用flask_sqlalchemy。

我有 10 名学生，分为 2 个收费类。

当 school_class_id 被传递时，我只需要返回特定类(class)的学生，其他学生。

第一个查询可以很好地列出所有学生。在第二个代码中，添加了 school_class_id ，也按照预期列出了特定类(class)的学生。

但是当我在 if 条件下添加 school_class_id 过滤器时，它只返回一名学生。

此代码可以很好地获取所有学生:

result = db.session.query(
                Student, SchoolClass, Guardian,
                func.sum(StudentFee.net_payable).label('total_payable'),
                ).filter(
                    and_(SchoolClass.school_id == user['school_id'],
                    SchoolClass.id == Student.school_class_id),
                    SchoolClass.status == GenEnum.ACTIVE.value).join(
                        StudentGuardian, StudentGuardian.student_id == Student.id).filter(
                            Guardian.id == StudentGuardian.guardian_id
            ).outerjoin(StudentFee,
                StudentFee.student_id == Student.id
                        ).filter(StudentFee.status == NewEnum.ACTIVE.value).group_by(Student.id)

result.all()

此代码在传递school_class_id时起作用:

result = db.session.query(
                Student, SchoolClass, Guardian,
                func.sum(StudentFee.net_payable).label('total_payable'),
                ).filter(
                    and_(SchoolClass.school_id == user['school_id'],
                    # SchoolClass.id == body.get('school_class_id'),
                    SchoolClass.id == Student.school_class_id),
                    SchoolClass.status == GenEnum.ACTIVE.value).join(
                        StudentGuardian, StudentGuardian.student_id == Student.id).filter(
                            Guardian.id == StudentGuardian.guardian_id
            ).outerjoin(StudentFee,
                StudentFee.student_id == Student.id
                        ).filter(StudentFee.status == NewEnum.ACTIVE.value).group_by(Student.id)

result.all()

但是稍后添加过滤器的代码存在一些问题，因为它只返回一名学生:

result = db.session.query(
                Student, SchoolClass, Guardian,
                func.sum(StudentFee.net_payable).label('total_payable'),
                )

if body.get('school_class_id'):
    result.filter(SchoolClass.id == body.get('school_class_id'))

result.filter(
        and_(SchoolClass.school_id == user['school_id'],
        SchoolClass.id == Student.school_class_id),
        SchoolClass.status == GenEnum.ACTIVE.value).join(
            StudentGuardian, StudentGuardian.student_id == Student.id).filter(
                Guardian.id == StudentGuardian.guardian_id
).outerjoin(StudentFee,
    StudentFee.student_id == Student.id
            ).filter(StudentFee.status == NewEnum.ACTIVE.value).group_by(Student.id)


result.all()

Answer 1

SQLAlchemy Query 对象是生成性的。换句话说，Query.filter() 等方法会生成一个新对象，而不是改变现有对象。在尝试添加条件过滤时，您生成并丢弃了 2 个新查询。结果只得到 1 行的原因是，您最终运行的实际查询有一个聚合，没有显式的 GROUP BY 子句，因此整个集合被视为一个组。修复很简单，只需在添加过滤器等时重新分配查询即可:

result = db.session.query(...)

if body.get('school_class_id'):
    result = result.filter(...)

result = result.filter(...).join(...).outerjoin(...).filter(...).group_by(...)

result.all()