CODEWITHSUNDEEP
laravellaravel-4sumrelation
Hardist
Hardist

Reputation: 1983

Laravel Sum of relation

I want to return the sum of "amount" from my payments table. There can be many payments for one invoice. The below "->sum('amount') does not work, it returns:

Call to a member function addEagerConstraints() on a non-object.

How to return the sum of all payments for each invoice in my relation?

Invoices Model:

class Invoices extends Eloquent {

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

Expenses Model:

class Payments extends Eloquent {

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

My table "payments" holds the foreign key of my tables invoices, which is invoices_id.

Upvotes: 1

Views: 33881

Answers (8)

qahtan said
qahtan said

Reputation: 71

if you want to add where condition use this

  ->withSum(['transaction' => function ($q) {
                return $q->where('status', 'success');
            }], 'amount')

Upvotes: 0

Shlok
Shlok

Reputation: 11

//Model Invoices

class Invoices extends Eloquent {

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

//Model Payments

class Payments extends Eloquent {

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

Add In your controller

Invoice::withSum("payments","amount")->get();

Upvotes: 1

Jakub Adamec
Jakub Adamec

Reputation: 1124

Starting by Laravel 8 you can simply use withSum() function.

use App\Models\Post;

$posts = Post::withSum('comments', 'votes')->get();

foreach ($posts as $post) {
    echo $post->comments_sum_votes;
}

https://laravel.com/docs/8.x/eloquent-relationships#other-aggregate-functions

Upvotes: 13

dipenparmar12
dipenparmar12

Reputation: 3455

This is also possible. we can do by model itself.

class Invoices extends Eloquent {

    public function payments()
    {
        return $this->hasMany('Payments')
            ->selectRaw('SUM(payments.amount) as payment_amount')
            ->groupBy('id'); // as per our requirements.
        }
    }
}

Note SUM(payments.amount)

payments is tableName

amount is fieldName

Upvotes: 2

Алексей Морозов
Алексей Морозов

Reputation: 187

You can show this package

$invoices = Invoices::withSum('payments:amount')->get();

Upvotes: 4

dakine
dakine

Reputation: 699

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();

;

Upvotes: 5

Hepworth
Hepworth

Reputation: 21

First decide which Invoice (for example id 1)

$invoice = Invoices::find(1);

Then eager load all the corresponding payments

$eagerload = $invoice->payments;

Finally assuming you have the amount field in your Invoice model you can simply find the sum using the method below:

$totalsum = $eagerload->sum('amount');

Upvotes: 2

scx
scx

Reputation: 2789

I found a simple way to acomplish this in here, you can use withPivot() method.

You can redefine a bit your relation to something like following

public function expenses()
{
    return $this->belongsToMany('Expenses', 'invoices_expenses')
                   ->withPivot('name', 'amount', 'date');
}

Upvotes: 1

Related Questions