How to do some constraint checking during knex/objectionjs raw query

Question

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?

Lee · Answer 1 · 2022-05-24T19:33:37.267

0

Can you do this?

amount: raw('cart_item.amount + ?', ((cart_item.amount + req.body.amount) >= 0) ? req.body.amount : 0 ),

edited May 24 '22 at 19:33

answered May 24 '22 at 19:24

Lee

29,398
28
117
170

that will run, but it won't achieve what I want. If `req.body.amount` is `-10`, and `amount` field's value is `20`, then final value `20-10 = 10` is greater than 0. I want to keep it as it is this time. – skylark May 24 '22 at 19:29
@skylark is there a way to reference the `amount` field value inline? – Lee May 24 '22 at 19:32
hey I was able to do it in the following way, but it seems nasty and not good. I will wait to see if there's any better way. And I don't actually know if amount field's value can be referenced as I want. There's a [ref()](https://vincit.github.io/objection.js/api/objection/#ref) function, but it doesn't seem to do what I want. `amount: raw('case when cart_item.amount + ? < 0 then 0 else cart_item.amount + ? end', [req.body.amount, req.body.amount])` – skylark May 24 '22 at 20:23

score 0 · Answer 2 · answered May 27 '22 at 08:33

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

How to do some constraint checking during knex/objectionjs raw query

2 Answers2