聚合函数表达式放在“FROM”子句中

Loan )按某些条件筛选并加入另一个模型( LoanPayment ).

第一个模型与第二个模型有一对多关系,我想收集第二个模型的一列上的聚合值。

模型定义如下:

class Loan(Base):
    __tablename__ = 'loan'
    id = Column(Integer, primary_key=True)
    granted_date = Column(Date, nullable=False)
    state = Column(String(50), default='GRANTED', index=True)
    value = Column(Numeric(19, 10, asdecimal=True),
                   nullable=False, default=money.Decimal(0))
    [... other columns ...]

class LoanPayout(Base):
    __tablename__ = 'loan_payout'
    id = Column(Integer, primary_key=True)
    payment_date = Column(Date, nullable=False)
    value = Column(Numeric(19, 10, asdecimal=True),
                   nullable=False, default=money.Decimal(0))
    loan_id = Column(Integer, ForeignKey('loan.id'),
                     nullable=False, index=True)
    loan = relationship('Loan',
                        backref=backref('payouts', lazy='dynamic',),
                        foreign_keys=[loan_id], order_by=payment_date.asc)
    [... other columns ...]

Loan.granted_date state='LATE' ),并返回 sum() 所有相关支出的价值,按贷款分组。

我尝试了以下orm查询:

session.query(
    Loan.id,
    Loan.granted_date,
    func.sum(LoanPayout.principal).alias('loan_payout_total'),
).filter(
    Loan.state == 'LATE',
).join(
    LoanPayout
).group_by(Loan.id)

SELECT
    loan.id AS loan_id,
    loan.sale_date AS loan_sale_date,
    loan_payout_total.sum_1 AS loan_payout_total_sum_1
FROM
    sum(loan_payout.principal) AS loan_payout_total,
    loan
JOIN loan_payout
    ON loan.id = loan_payout.loan_id
WHERE
    loan.state = %(state_1)s
GROUP BY
    loan.id

查询几乎和我期望的一样:它正确地连接两个表,呈现

但它把原本应该是 总和() FROM 节而不是 SELECT 章节:

FROM
    sum(loan_payout.principal) AS loan_payout_total,
    loan

然后它将一个别名列放入 选择 条款:

    loan_payout_total.sum_1 AS loan_payout_total_sum_1

如何使用ORM支持让SQLAlchemy正确呈现这个查询?