英文:
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;
});
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论