Create a record while selecting data from different table in prisma and both have the same relation to the parent table

huangapple go评论70阅读模式
英文:

Create a record while selecting data from different table in prisma and both have the same relation to the parent table

问题

这是表格:

model wallet {
  id             Int  @id @default(autoincrement())
  userId         Int  @unique
  balance        Int  @default(0)
  joining_bonus  Int  @default(0)
  referral_bonus Int  @default(0)
  incentive      Int  @default(0)
  user           user @relation(fields: [userId], references: [id])
}

model transaction {
  id               Int                @id @default(autoincrement())
  userId           Int
  type             String
  amount           Int
  curBalance       Int
  payment_gateway String
  payment_id       String
  status           transaction_status @default(PENDING)
  timestamp        DateTime           @default(now())
  user             user               @relation(fields: [userId], references: [id])
}

我想执行这个查询:

const transaction = await prisma.transaction.create({
    data: {
        amount: amount,
        type: type,
        payment_gateway: payment_gateway,
        payment_id: payment_id,
        curBalance: {
            // 从钱包表中获取数据并选择balance字段
        },
        user: {
            connect: {
                id: parseInt(req.user.id)
            }
        }
    }
});

如果我在不同的查询中执行这个操作,可能会由于异步调用而导致时间差,从而获得不同的当前余额值。

如果这个查询是可行的,请告诉我如何操作,如果不行,如何操作才能始终获得最新的数据。

英文:

These are the table

model wallet {
  id             Int  @id @default(autoincrement())
  userId         Int  @unique
  balance        Int  @default(0)
  joining_bonus  Int  @default(0)
  referral_bonus Int  @default(0)
  incentive      Int  @default(0)
  user           user @relation(fields: [userId], references: [id])
}

model transaction {
  id               Int                @id @default(autoincrement())
  userId           Int
  type             String
  amount           Int
  curBalance       Int
  payment_gateway String
  payment_id      String
  status           transaction_status @default(PENDING)
  timestamp        DateTime           @default(now())
  user             user               @relation(fields: [userId], references: [id])
 
}

and i want to execute this query

const transaction = await prisma.transaction.create({
    data: {
        amount: amount,
        type: type,
        payment_gateway: payment_gateway,
        payment_id: payment_id,
        curBalance: {
            //get data from wallet table and select balance field
        },
        user: {
            connect: {
                id: parseInt(req.user.id)
            }
        }
        
    }
});

I want to get the data from wallet table while creating the of the transaction record because if i did this in different queries there can be time difference due to async calls and i can get different value of current Balance.

If this query is possible tell me how or if not how to do this so i can always the latest data.

答案1

得分: 3

Prisma目前不支持子查询或在创建记录时从另一个表中获取数据,以您尝试实现的方式。

为了实现这一点,您需要使用事务来确保这些操作以原子方式并按正确的顺序发生。首先,您需要获取用户的当前余额,然后在创建交易记录时使用该数据。

您可以使用数据库事务,在事务中读取余额值时,可以使用数据库级别的锁定,比如SELECT FOR UPDATE,以防止在事务完成时进行更新。

以下是一个示例:

const userId = parseInt(req.user.id);

// 开始一个事务
const result = await prisma.$transaction(async (prisma) => {
  // 锁定行
  const wallet = await prisma.$queryRaw`SELECT * FROM "wallet" WHERE "userId" = ${userId} FOR UPDATE`;

  // 执行您的更新操作...
  const transaction = await prisma.transaction.create({
    data: {
      amount: amount,
      type: type,
      payment_gateway: payment_gateway,
      payment_id: payment_id,
      curBalance: wallet.balance,
      user: {
        connect: {
          id: userId,
        },
      },
    },
  });

  // 返回结果
  return transaction;
});
英文:

Prisma currently doesn't support sub-selects or fetching data from another table while creating a record in the way that you are trying to achieve.

In order to accomplish this, you will need to use a transaction to ensure that these operations happen atomically and in the correct order. You would first fetch the current balance for the user, then use that data in the creation of the transaction record.

You can use a Database Transaction and while reading the balance value in the transaction, you could use a database level lock like SELECT FOR UPDATE to prevent it from updating while the transaction completes.

Here's an example of how it could look like:

const userId = parseInt(req.user.id);

// Start a transaction
const result = await prisma.$transaction(async (prisma) => {
  // Lock the row
  const wallet = await prisma.$queryRaw`SELECT * FROM "wallet" WHERE "userId" = ${userId} FOR UPDATE`;

  // Perform your updates...
  const transaction = await prisma.transaction.create({
    data: {
      amount: amount,
      type: type,
      payment_gateway: payment_gateway,
      payment_id: payment_id,
      curBalance: wallet.balance,
      user: {
        connect: {
          id: userId,
        },
      },
    },
  });

  // Return the results
  return transaction;
});

huangapple
  • 本文由 发表于 2023年6月5日 19:33:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76406012.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定