NestJS TypeORM（PostgreSQL）批量更新多记录：累加金额并更新日期

我有一个实体包含以下字段：

@Column({ name: "total_amount_paid", type: "decimal", precision: 10, scale: 2, nullable: true })
totalAmountPaid: number;

@Column({ name: "last_paid_date", type: "date", nullable: true })
lastPaidDate: Date;

我正在使用基于事务的查询，使用EntityManager的代码如下：

async _updateAgreementsForPayment(
    queryRunnerManager: EntityManager, agreements: AgreementDto[], paidDate: Date
  ): Promise<UpdateResult> {
    const agreementIds = agreements.map((agreement) => agreement.id);
    // Save the updated entities
    return queryRunnerManager.update(
      AgreementEntity, // Target entity
      agreementIds, // Conditions
      { lastPaidDate: paidDate } // Values to be updated
    );
  }

现在我希望在更新lastPaidDate的同时，按照totalAmountPaid = totalAmountPaid + agreementDto.paidAmount的规则更新totalAmountPaid字段，请问是否可以通过批量更新实现？

可以实现，需根据不同场景选择对应方式：

场景1：所有协议的paidAmount值相同 #

如果要更新的所有协议的paidAmount是同一个固定值，直接用EntityManager.update结合SQL表达式即可完成批量更新：

return queryRunnerManager.update(
  AgreementEntity,
  agreementIds,
  {
    lastPaidDate: paidDate,
    totalAmountPaid: () => "total_amount_paid + :paidAmount",
  },
  { parameters: { paidAmount: 固定金额 } }
);

这种方式会生成单条批量更新SQL，执行效率很高。

场景2：每个协议的paidAmount值不同 #

此时常规批量update无法直接处理（每条记录的增量不同），可以通过QueryBuilder构建CASE WHEN语句实现单条SQL批量更新：

async _updateAgreementsForPayment(
  queryRunnerManager: EntityManager, agreements: AgreementDto[], paidDate: Date
): Promise<UpdateResult> {
  const queryBuilder = queryRunnerManager
    .createQueryBuilder()
    .update(AgreementEntity)
    .set({ lastPaidDate: paidDate })
    .set("total_amount_paid", () => {
      let caseStmt = "CASE";
      agreements.forEach(agreement => {
        caseStmt += ` WHEN id = ${agreement.id} THEN total_amount_paid + ${agreement.paidAmount}`;
      });
      caseStmt += " ELSE total_amount_paid END";
      return caseStmt;
    })
    .where("id IN (:...ids)", { ids: agreements.map(a => a.id) });

  return queryBuilder.execute();
}

该方式会生成一条包含CASE WHEN逻辑的UPDATE语句，一次性完成所有记录的更新，避免多次数据库请求。

如果数据量极小，也可以循环调用update逐个更新，但这种方式会产生多条SQL，性能远不如单条CASE WHEN方案。

内容的提问来源于stack exchange，提问作者Code Guru