SQLAlchemy子查询计算另一表的总和

Question

我在理解如何在SQLAlchemy中正确执行子查询时遇到了困难，特别是当我需要返回值的时候（也就是说，不是在WHERE条件中使用）。

我使用的是声明式方法。

我正在使用两个模型：

class ProjectInvoices(Base):
    InvoiceID = Column(Integer(unsigned=True), default=0, primary_key=True, autoincrement=True)
    MasterProjectID = Column(Integer(unsigned=True), index=True, nullable=False)
    ExpenseAmount = Column(Numeric(10, 2), default=0)
    HoursAmount = Column(Numeric(10, 2), default=0)
    IsVoid = Column(Boolean, default=0, index=True)
    IsSubmit = Column(Boolean, default=0, index=True)

class ProjectMasters(Base):
    MasterProjectID = Column(Integer(unsigned=True), default=0, primary_key=True, autoincrement=True)
    MasterProjectName = Column(Unicode(255))
    MasterProjectMemo = Column(UnicodeText)
    IsActive = Column(Boolean, default=0, index=True)

这个查询的目的是通过一个子查询来计算每个项目当前的发票金额，方法是对相关的发票进行求和。之所以选择用子查询而不是简单的连接，还有其他原因，所以我真的需要搞清楚子查询的问题。

这是我目前的SA查询示例：

sqry = session.query(
  func.sum(
    func.ifnull(ProjectInvoices.ExpenseAmount, 0) 
    + func.ifnull(ProjectInvoices.HoursAmount, 0))).label('billed_total')
).filter(and_(ProjectInvoices.IsVoid == 0, ProjectInvoices.IsSubmit == 1)
).subquery()

result = session.query(
  ProjectMasters.MasterProjectID, 
  ProjectMasters.MasterProjectName, 
  sqry.columns.billed_total.label('invoice_total')
).filter(ProjectMasters.IsActive == 1).all()

我感觉这个问题可能很简单，但我就是无法找到解决办法。

我尝试了几乎所有能找到的示例，结果都不太理想。如果我不加 .correlate() 参数，就会收到以下错误：

'Alias' object has no attribute 'MasterProjectID'

我还尝试在子查询的末尾添加以下语句，但没有成功：

.correlate(ProjectMasters.MasterProjectID, ProjectInvoices.MasterProjectID)

如果我加上correlate参数，就会收到以下错误：

TypeError: Boolean value of this clause is not defined

提前感谢你的帮助……

Answer 1

通常我会使用一个叫做 column_property 的东西来处理这种需求，比如说：

class ProjectMasters(Base):
    ...

    billed_total = column_property(
        select(
            [func.sum(
                func.coalesce(ProjectInvoices.ExpenseAmount, 0)
                + func.coalesce(ProjectInvoices.HoursAmount, 0)
            )],
            and_(
                MasterProjectID == ProjectInvoices.MasterProjectID,
                ProjectInvoices.IsVoid == False,
                ProjectInvoices.IsSubmit == True,
            ),
        ).label('billed_total'),
        deferred=True,
    )

之后，你就可以像使用普通属性一样使用它，比如：

result = session.query(
    ProjectMasters.MasterProjectID, 
    ProjectMasters.MasterProjectName, 
    ProjectMasters.billed_total.label('invoice_total'),
).filter(ProjectMasters.IsActive == 1).all()