I am doing some raw query binding in objectionjs and my code is as follows:
const item = await cart.$relatedQuery('items').insert(req.body)
.onConflict(['cartId', 'productId'])
.merge({
amount: raw('cart_item.amount + ?', req.body.amount),
price: raw('cart_item.price + ?', req.body.price)
});
Values for req.body.amount and req.body.price can be both positive and negative. Because of this, at some point the final value for amount and price fields in the database might become negative too after the addition/subtraction. And I want to prevent that from happening. What should I do in such case?
I tried the following:
amount: Math.max(raw('cart_item.amount + ?', req.body.amount), 0)
But this doesn't work (of course it shouldn't) and returns NaN. I don't want to put an .unsigned() check on the field itself, but check during runtime. Is there any way I can get what I want without making multiple queries to fetch the values for amount and price beforehand and checking before inserting the new values?
Can you do this?
amount: raw('cart_item.amount + ?', ((cart_item.amount + req.body.amount) >= 0) ? req.body.amount : 0 ),
the way i would do it at runtime is using transactions:
const trx = await Item.startTransaction()
try {
const item = await cart
.$relatedQuery('items', trx) // make sure to pass the transaction
.insert(req.body)
.onConflict(['cartId', 'productId'])
.merge({
amount: raw('cart_item.amount + ?', req.body.amount),
price: raw('cart_item.price + ?', req.body.price)
});
if (item.price < 0 || item.amount < 0 ) {
// this will trigger the catch block and revert the operation
throw new Error(...)
}
// success return as response or something
res.json(item)
await trx.commit()
} catch (err) {
await trx.rollback()
return next(err)
}