Sum columns A and B and add the value in column C

I know this question may seem a little basic, but I don’t know how I can do it.

I want to sum columns valor_unitario and quantidade of the table, to add the value inside column total of the same table, is this possible? If so, how can I do it?

migration:

up () {
    this.create('nota_postos', table => {
      table.increments()
      table
        .integer('posto_id')
        .unsigned()
        .references('id')
        .inTable('postos')
        .onUpdate('CASCADE')
        .onDelete('SET NULL')
      table
        .integer('veiculo_id')
        .unsigned()
        .references('id')
        .inTable('veiculos')
        .onUpdate('CASCADE')
        .onDelete('SET NULL')
      table
        .integer('motorista_id')
        .unsigned()
        .references('id')
        .inTable('funcionarios')
        .onUpdate('CASCADE')
        .onDelete('SET NULL')
      table.date('data').notNullable()
      table.string('numero_ordem').notNullable()
      table.float('valor_unitario').notNullable()
      table.float('quantidade').notNullable()
      table.float('total').notNullable()
      table.timestamps()
    })
  }

  down () {
    this.drop('nota_postos')
  }

model:

class NotaPosto extends Model {
  posto () {
    return this.belongsTo('App/Models/Posto')
  }

  veiculo () {
    return this.belongsTo('App/Models/Veiculos')
  }

  motorista () {
    this.belongsTo('App/Models/Funcionario')
  }
}

controller:

  async store ({ request }) {
    const notaPosto = await NotaPosto.create(request.body)

    await notaPosto.load('posto', 'veiculo', 'motorista')

    return notaPosto
  }

I came to help you but I am giving up when I understood the goal you are trying to achieve:

I want to sum columns valor_unitario and quantidade of the table, to add the value inside column total of the same table,

I do not like those last 2 words: “same table”. You must not do that otherwise your database is not normalized.

Design thoughtfully before you jump to code.

2 Likes

Hi!

It might be more better to have sum in computed property in Lucid model
https://adonisjs.com/docs/4.1/database-getters-setters#_computed_properties

But if you still need sum to be present in database for some reason I’d suggest you use database triggers for it. Setting up trigger depends on underlying database, but here is some example about trigger that adds value1 and value2 together into field value3

1 Like

I would go with the two answers above. If you want a column at the database level, see the DB documentation for a computed column. For e.g. in mysql you can create generated columns. http://www.mysqltutorial.org/mysql-generated-columns/