Nestjs pgsql typeorm : update many records based on different criteria

Question

I have an entity having these fields

@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;

I am using transaction base query so using query manager like this

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
    );
  }

now along with lastPaidDate i want to update the totalAmountPaid that should be calculated like

totalAmountPaid = totalAmountPaid + agreementDto.paidAmount

is it possible via update many or something?

Answer 1

If agreementDto.paidAmount the same for all records you can use:

  queryRunnerManager.update(
    AgreementEntity,
    agreementIds,
    {
      lastPaidDate,
      totalAmountPaid: () => `totalAmountPaid + ${paidAmount}`
    }
  );

if paidAmount may vary from record to record, you can use CASE:

  queryRunnerManager.update(
    AgreementEntity,
    agreementIds,
    {
      lastPaidDate,
      totalAmountPaid: () => {
        return `
          amount + CASE
            ${agreements.map((agreementDto) => `WHEN id = ${agreementDto.id} THEN amount + ${agreementDto.paidAmount}`).join(' ')}
            ELSE amount
          END
        `
      }
    }
  );

but be carefully because in such form you should have some check to prevent SQL injection.