基于其他行的Sqlalchemy列属性

Question

我有模型expense和income，它们分别有多对多关系refunds和expenses。我想在expense表上创建一个列refunded_amount属性，该属性被称为refunded_amount，并根据以下伪代码填充（开销由相同的refund.income_id过滤）：

expenses = [exp1, exp2]
payment = income.amount
for exp in expenses:
    amt = exp.amount
    if amt <= payment:
        exp.refunded_amount += amt
        payment -= amt
    else:
        exp.refunded_amount = payment
        payment = 0

如何将其转换为sqlalchemy并将其包含在refunded_amount列属性的select语句中？这些是目前的模型，但是像这样，每个支出的refunded_amount包含了与特定收入相关的所有支出的总和。你知道吗

refund_table = Table(
    "refund", Base.metadata,
    Column("expense_id", Integer, ForeignKey("expense.id"), primary_key=True),
    Column("income_id", Integer, ForeignKey("income.id"), primary_key=True)
)


class Income(Base):
    __tablename__ = "income"

    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)
    amount = Column(Float)
    refund = Column(Boolean, default=False, nullable=False)
    expenses = relationship("Expense", secondary=refund_table,
                            back_populates="refunds")


class Expense(Base):
    __tablename__ = "expense"

    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)
    refunds = relationship("Income", secondary=refund_table,
                           back_populates="expenses")

    refunded_amount = column_property(
        select(
            [func.sum(Income.amount)],
            and_(
                refund_table.c.expense_id == id,
                refund_table.c.income_id == Income.id
            ),
            refund_table
        ).label("refunded_amount")
    )

重要的是，如果可能的话，我希望使用声明性的，并避免退回到经典的Python@property，我可以在其中使用对象的当前会话，因为，如sqlalchemy docs中所述：

The plain descriptor approach is useful as a last resort, but is less performant in the usual case than both the hybrid and column property approaches, in that it needs to emit a SQL query upon each access.

Answer 1

如果只取返回所需结果的Expense.amount和sum(Income.amount)中的最小值：

refunded_amount = column_property(
    select(
        [func.min(amount, func.sum(Income.amount))],
        and_(
            refund_table.c.expense_id == id,
            refund_table.c.income_id == Income.id
        ),
        refund_table
    ).label("refunded_amount")
)