除非提交两次，否则KneX js、Sqlite数据库不会更新

jmo0nnb3 于 2022-11-15 发布在 SQLite

关注(0)|答案(1)|浏览(141)

我有一个名为“ACCOUNTS”的表，我想在编辑“TOTAL”列时更新名为“BALANCE”的列。当我提交数据时，它只更新“合计”列。但如果我再次提交(两次)，则“BALANCE”列将更新。
以下是代码：

```function updateOne(id, job) {
  return db
    .transaction(function (t) {
      return db("jobs")
        .where({ id })
        .transacting(t)
        .select()
        .update({
          job_title: job.job_title,
          job_description: job.job_description,
          in_progress: job.in_progress,
          due_date: job.due_date,
          customer_id: job.customer_id,
          assigned_to: job.assigned_to,
          admin_id: job.admin_id,
          updated_at: timestamp,
        })
        .then(() => {
          return db("payments")
            .transacting(t)
            .sum("amount_paid as sum")
            .where("account_id", id);
        })
        .then((amountPaid) => {
          let { sum } = amountPaid[0];
          return db("accounts")
            .transacting(t)
            .where({ id })
            .update({
              total: job.total,
              balance: db.raw(`total - ${sum}`),
            });
        })
        .then(t.commit)
        .catch(t.rollback);
    })
    .then(() => {
      console.log("transaction succeeded ", id);
      return findById(id);
    })
    .catch((err) => {
      console.log("transaction failed", err);
    });```

sqlite

来源：https://stackoverflow.com/questions/73628067/knex-js-sqlite-database-wont-update-unless-submitted-twice

1条答案

按热度按时间

vdgimpew1#

我像这样修复了我的代码，它起作用了。我删除了ACCOUNT表，并从JOB表->BALANCE列进行计算。

async function update(id, job) {
  try {
    await db.transaction(async (trx) => {
      const ids = await trx("job")
        .where("job.id", id)
        .update(
          {
            title: job.title,
            description: job.description,
            inProgress: job.inProgress,
            dueDate: job.dueDate,
            customerId: job.customerId,
            userId: job.userId,
            adminId: job.adminId,
            total: job.total,
            updatedAt: timestamp,
          },
          "id"
        )
        .transacting(trx);

      const payment = await trx("payment")
        .where("payment.jobId", ids[0].id)
        .update(
          {
            updatedAt: timestamp,
            userId: job.userId,
            paymentType: job.paymentType,
            amountPaid: job.amountPaid,
            editedBy: job.adminId,
          },
          ["id", "jobId"]
        )
        .transacting(trx);
      const [result] = payment;

      // Calculate all payments
      const paymentTotal = await trx("payment")
        .where("jobId", result.jobId)
        .sum("amountPaid as sum")
        .transacting(trx);

      let sum = paymentTotal[0].sum;
      // Calculate the new balance
      const balance = await trx("job")
        .where("id", result.jobId)
        .update({
          balance: db.raw(`job.total - ${sum}`),
        })
        .transacting(trx);
      
      return findById(ids[0].id);
    });
  } catch (error) {
    console.log(error);
  }
}

赞(0）回复(0）举报 2022-11-15

我来回答

除非提交两次，否则KneX js、Sqlite数据库不会更新

1条答案

相关问题

热门标签

最新问答