Update table by using computed properties V5

[WeyIin]

So basically, I have a table that contains a few columns and in a model I have some computed properties.

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Networth extends BaseSchema {
  protected tableName = 'networth'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.integer('property_value').unsigned().notNullable().defaultTo(0)
      table.integer('cars_value').unsigned().notNullable().defaultTo(0)
      table.integer('other_value').unsigned().notNullable().defaultTo(0)
      table.string('value', 20).notNullable()
      table.timestamps(true)
    })
  }

  public async down () {
    this.schema.dropTable(this.tableName)
  }
}

And a computed property:

  @column()
  public propertyValue: number

  @column()
  public carsValue: number

  @column()
  public otherValue: number

...
...

  @computed()
  public get sumNetworth () {
    return this.propertyValue + this.carsValue + this.otherValue
  }

Obviously, sumNetworth will sum these values but they will fluctuate every so and then so the table column value will have to be updated periodically for all the users. The value column will range from Very Low to Very High. For some reason, I don't want this to be computed property.

I was thinking if there is a possibility to update all rows with one query using computed properties (and I really don't want to run a loop). If it's possible to do that with ORM will save me some time otherwise I'd need to opt for raw mysql.

E.g.

Networth.query().where(sumNetworth > 100000).update('Very High')

[thetutlage]

The computed have nothing to do with the update query or updating columns. I am little bit confused with your question. But anyways, you can use an update request as

Networth.query().update({
  column1: 'value',
  column2: 'value',
  column3: 'value'
})

Also, you cannot add a where clause on a column that does exists in the database

[WeyIin]

Hi @thetutlage - thanks for your reply.

I wasn't quite clear of what I am trying to achieve.

Networth table will be linked to users, and there will be as many rows as there are users.

Computed property will return a sum of the columns for each user and value column will have to be updated against what is the computed property sum like.

For example, for users having computed property sum over 1 million, the value will be updated to Very High, but for those having sum like 5,000 it will be updated to Very Low.

[WeyIin]

Raw MySql query looks something like this:

UPDATE networth
    SET value= (CASE
                         when property_value + cars_value + other_value <= 5000 THEN 'Very Low'
                         when property_value + cars_value + other_value > 100000 THEN 'High'
                         when property_value + cars_value + other_value > 10000000 THEN 'Very High'
                    END)

I was hoping I could use a computed property (which already sums those up) and ORM to do something like that.