Laravel Eloquent Sum of Relation's Column

Laravel Eloquent Sum of relation's column

Auth::user()->products->sum('price');

The documentation is a little light for some of the Collection methods but all the query builder aggregates are seemingly available besides avg() that can be found at http://laravel.com/docs/queries#aggregates.

Laravel: How to get SUM of a relation column using Eloquent

You need sub query to do that. I'll show you some solution:

• Solution 1

  $amountSum = Transaction::selectRaw('sum(amount)')
      ->whereColumn('account_id', 'accounts.id')
      ->getQuery();
  
  $accounts = Account::select('accounts.*')
      ->selectSub($amountSum, 'amount_sum')
      ->get();
  
  foreach($accounts as $account) {
      echo $account->amount_sum;
  }
  

• Solution 2

  Create a withSum macro to the EloquentBuilder.

  use Illuminate\Support\Str;
  use Illuminate\Database\Eloquent\Builder;
  use Illuminate\Database\Query\Expression;
  
  Builder::macro('withSum', function ($columns) {
      if (empty($columns)) {
          return $this;
      }
  
      if (is_null($this->query->columns)) {
          $this->query->select([$this->query->from.'.*']);
      }
  
      $columns = is_array($columns) ? $columns : func_get_args();
      $columnAndConstraints = [];
  
      foreach ($columns as $name => $constraints) {
          // If the "name" value is a numeric key, we can assume that no
          // constraints have been specified. We'll just put an empty
          // Closure there, so that we can treat them all the same.
          if (is_numeric($name)) {
              $name = $constraints;
              $constraints = static function () {
                  //
              };
          }
  
          $columnAndConstraints[$name] = $constraints;
      }
  
      foreach ($columnAndConstraints as $name => $constraints) {
          $segments = explode(' ', $name);
  
          unset($alias);
  
          if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
              [$name, $alias] = [$segments[0], $segments[2]];
          }
  
          // Here we'll extract the relation name and the actual column name that's need to sum.
          $segments = explode('.', $name);
  
          $relationName = $segments[0];
          $column = $segments[1];
  
          $relation = $this->getRelationWithoutConstraints($relationName);
  
          $query = $relation->getRelationExistenceQuery(
              $relation->getRelated()->newQuery(),
              $this,
              new Expression("sum(`$column`)")
          )->setBindings([], 'select');
  
          $query->callScope($constraints);
  
          $query = $query->mergeConstraintsFrom($relation->getQuery())->toBase();
  
          if (count($query->columns) > 1) {
              $query->columns = [$query->columns[0]];
          }
  
          // Finally we will add the proper result column alias to the query and run the subselect
          // statement against the query builder. Then we will return the builder instance back
          // to the developer for further constraint chaining that needs to take place on it.
          $column = $alias ?? Str::snake(Str::replaceFirst('.', ' ', $name.'_sum'));
  
          $this->selectSub($query, $column);
      }
  
      return $this;
  });
  

  Then, you can use it just like when you're using withCount, except you need to add column that need to sum after the relationships (relation.column).

  $accounts = Account::withSum('transactions.amount')->get();
  
  foreach($accounts as $account) {
      // You can access the sum result using format `relation_column_sum`
      echo $account->transactions_amount_sum;
  }
  

  $accounts = Account::withSum(['transactions.amount' => function (Builder $query) {
      $query->where('status', 'APPROVED');
  })->get();
  

Laravel 8 Sum column of related table

You can use withSum method. This method accept the relation method as first argument and column to sum as second argument

$lastInvoices = Invoice::latest()
                          ->limit(10)
                          ->withSum('invoiceLines','your_column_name')
                          ->with(['invoiceLines'])->get();

Laravel Eloquent - Sum multiple relation columns

I managed to make the query using only eloquent. My database performance has changed dramatically as expected.

This is the query using only eloquent:

$invoice = Invoice::number($number)
    ->selectRaw('invoices.uuid as uuid,
    invoices.number_id as number_id,
    count(*) as contacts,
    sum(incoming_messages) as incoming_messages,
    sum(outgoing_messages) as outgoing_messages,
    sum(outgoing_template_messages) as outgoing_template_messages,
    invoices.started_at,
    invoices.ended_at')
    ->join('contacts', 'contacts.invoice_id', '=', 'invoices.id')
    ->groupBy('invoices.id')
    ->latest('started_at')
    ->firstOrFail();

---

Update

I got into a problem where my query didn't return invoices that didn't have any contacts, I had to make a small change.

The join() method generates an inner join, the inner join does not include rows from table A that have no elements in table B.

To overcome this situation, a left join must be used. However, if the left join has no values in table B, it was returning my sums as null.

To solve this I simply used the MySql function ifnull().

This is the final query:

$invoices = Invoice::number($number)
    ->selectRaw('invoices.uuid as uuid,
    invoices.number_id as number_id,
    ifnull(count(contacts.id), 0) as contacts,
    ifnull(sum(incoming_messages), 0) as incoming_messages,
    ifnull(sum(outgoing_messages), 0) as outgoing_messages,
    ifnull(sum(outgoing_template_messages), 0) as outgoing_template_messages,
    invoices.started_at,
    invoices.ended_at')
    ->leftJoin('contacts', 'contacts.invoice_id', '=', 'invoices.id')
    ->groupBy('invoices.id')
    ->firstOrFail();

laravel 7 Get Sum From eloquent relation column

You can use withCount method with callback like this.

$user = User::with(['affiliateCode','rewardPoints','orders'=>function($query){
        $query->where('order_status_id','!=',1);
    }])
    ->withCount(['orders'=>function($q){
        $q->where('order_status_id','!=',1);   
    }])
    ->withCount(['orders as orders_amount'=>function($q){
        $q->where('order_status_id','!=',1)->select(DB::raw('sum(amount)'));   
    }])
    ->where('id',$userId)->first();

Laravel Sum of relation

class Invoices extends Eloquent {

    public function payments()
    {
        return $this->hasMany('Payments');
    }
}

class Payments extends Eloquent {

    public function invoices()
    {
        return $this->belongsTo('Invoices');
    } 
}

In your controller

Invoice::with(['payments' => function($query){
   $query->sum('amount');
}])->get();

;

---

Related Topics