Orange Sum and Subtraction Update?

Question

I’m starting to learn how to use the Framework Laravel 5.4 for this I am making a personal finance system and I have a table of Conta with the countryside (Saldo);

I would like to make a transaction of Credit or Debit that will Subtract or Add the value of Saldo with the transaction-by-transaction.

I can do it in SQL, but what is the best way to do it in Laravel?

Answer 1

There are two ways that can be implemented that one of them is by eloquent ORM Model and the other Database: Query Builder

• Eloquent ORM Model

Create a class to represent your table as follows:

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;    
class Conta extends Model
{
    protected $table = 'conta';
    protected $fillable = ['cliente_id','saldo'];
    public $timestamps = false;    

    public function setSomaSaldo($saldo)
    {
        // Conta Existente
        if ($this->exists)
        {
            $this->attributes['saldo'] =
                $this->attributes['saldo'] + $saldo;
            $this->save();
        }
        return $this;
    }

    public function setSubtrairSaldo($saldo)
    {
        // Conta Existente
        if ($this->exists)
        {
            $this->attributes['saldo'] =
                $this->attributes['saldo'] - $saldo;
            $this->save();
        }
        return $this;
    }
}

that will contain two methods one to add up balance and the other to subtract the balance, following that the account exists for these two methods to work. To use the methods do:

• Adding up:

  $client_id = 1;
  $saldo = 150.69;
  $d = App\Models\Conta::where('cliente_id', $client_id)->first();
  if ($d) 
  {
      $d->setSomaSaldo($saldo);
  }
  

• Subtracting:

  $client_id = 1;
  $saldo = 150.69;
  $d = App\Models\Conta::where('cliente_id', $client_id)->first();
  if ($d) 
  {
      $d->setSubtrairSaldo($saldo);
  }
  

in that method setSomaSaldo($saldo) will add the value and then save in the table the change, and this happens also in the setSubtrairSaldo($saldo) subtract the amount of the current balance and save the changes.

---

• Database: Query Builder

By the method increment/decrement you can add or subtract by Query Builder simple and with excellent performance:

• Adding up:

  $client_id = 1;
  $saldo = 150.69;
  $status = \DB::table('conta')
         ->where('cliente_id',$client_id)
         ->increment('saldo', $saldo);
  if ($status) // atualizado com sucesso
  {
  }
  

• Subtracting:

  $client_id = 1;
  $saldo = 150.69;
  $status = \DB::table('conta')
         ->where('cliente_id',$client_id)
         ->decrement('saldo', $saldo);
  if ($status) // atualizado com sucesso
  {
  }
  

Observing: here also works class eloquent with Builder likewise observe:

• Adding up:

  App\Models\Conta::where('cliente_id', $cliente_id)
               ->increment('saldo', $saldo);
  

• Subtracting:

  App\Models\Conta::where('cliente_id', $cliente_id)
               ->decrement('saldo', $saldo);
  

---

In his comment was used a Running Raw SQL Queries which is also a great solution, example:

$client_id = 1;
$saldo = 150.69;
$params = [$saldo, $client_id];
\DB::update('UPDATE `conta` SET saldo = saldo + ? WHERE Cliente_id = ?', $params);

Remarks: the last two (Database: Query Builder and Running Raw SQL Queries) has a better performance than the first, because, the first runs 2 SQL to carry out the operation and the last two only 1 SQL already performs the operation, but, it is worth remembering that in many cases the first would be the most recommended, example common insertions and updates, so it is at your discretion which of the 3 solutions can be used.

_References:

• Eloquent - ORM Model
• Database: Query Builder
• Database: Query Builder methods: increment/decrement