Database/ORM Raw Statement Update

[weretyczx]

Quickly two questions here

1. increment/decrement helper methods only on @ioc:Adonis/Lucid/Database but why not in the Model ORM?

2. How to update a Model ORM/Database with a SQL statement
   Example:

await Database.from('user_assets')
    .where('coin_id', asset.coin_id)
    .where('user_id', asset.user_id)
    .update({
        'qty': Database.raw('`qty` + ' + qty),
        'freeze': Database.raw('freeze - ' + qty),
    })

Getting errors like this

  update `user_assets` set `qty` = {"sql":"`qty` + 10"}, `freeze` = {"sql":"freeze - 10"} where `coin_id` = 1 and `user_id` = 1 - Unknown column 'bindings' in 'field list'

If putting the string type statement, it will not be extractable as the same

await Database.from('user_assets')
      .where('coin_id', asset.coin_id)
      .where('user_id', asset.user_id)
      .update({
        'qty': '`qty` + ' + qty,
        'freeze': '`freeze` - ' + qty,
      })

   update `user_assets` set `qty` = '`qty` + 10', `freeze` = '`freeze` - 10' where `coin_id` = 1 and `user_id` = 1 - Incorrect decimal value: '`qty` + 10' for column `petnft_test`.`user_assets`.`qty` at row 1

Any idea can resolve this issue using ORM/Database instance?

[tomgobich]

1. increment/decrement are available via the Model Query Builder: await User.query().increment('someProperty')
2. I'm a bit confused about what you're trying to do here. Is qty a type of decimal? If so, in your second example you can see the error is stating Incorrect decimal value: 'qty' + 10'. You should be able to just do:

await Database.from('user_assets')
      .where('coin_id', asset.coin_id)
      .where('user_id', asset.user_id)
      .update({
        qty: qty,
        freeze: 'freeze - ' + qty,
      })

Update takes a key/value object pair, so there's no need to transform it to raw SQL yourself unless you're doing something more advanced. The key is the db property and the value is the value you want to update that property with.

[weretyczx]

1. Really thanks for your help 🙏
2. Yes. qty type is decimal, which means balance amount, and the scenario is to unfreeze the balance and refill to the current amount, if using the js way to handle the scenario might cause the time racing issue,
   so will be trying on pure SQL command update the balance

SQL command just like bellow

update `user_assets` set `qty` = `qty` + 10', `freeze` = `freeze` - 10 where `coin_id` = 1 and `user_id` = 1 

[thetutlage]

I think you can express the same query as follow.

User
  .query()
  .where('user_id', 1)
  .where('coin_id', 1)
  .increment('qty', 10) // same as `qty + 10`
  .decrement('freeze', 10) // same as `freeze - 10`

Also, whenever in doubt, just enable query debugging to see the query sent to the database server.

[Azq2]

@thetutlage Why doesn't the lucid model support atomic incr/decr operations?
They are not difficult to implement, but very useful.