Laravel query for multiple relationships

Question

I have a required to calculate the invoices status - Open, Paid, Overdue.

My table structure:

invoices:
- id
- custome_id
- total_amount
...

invoice_payments
- id
- invoice_id
- amount_received
...

Trying:

$records->whereHas('invoice', function($query) use ($val) {
    $query->whereHas('invoicePayments', function($_query){
        $_query->sum('amount_received');
    });
});

My concern is to get the status for the invoice - if invoice->sum('amount') > invoice->invoicePayment->sum('amount_received').

I want to do it with the help of whereHas functions not with database raw queries. Please suggest the way to do it.

Please post what you have tried, and where you became stuck. — fubar
– fubar, Commented Apr 13, 2018 at 2:05
I don't think you're going to be able to use a whereHas clause, because you need to compare the value from two different tables. Whereas a whereHas just executes a nested sub query. You'll have to use a join instead. — fubar
– fubar, Commented Apr 13, 2018 at 2:24
And this - My concern is to get the status for the invoice - doesn't make sense. What does it mean? — fubar
– fubar, Commented Apr 13, 2018 at 2:24
I need to search through all invoices for say like "Paid" status. — Xyrin Technologies
– Xyrin Technologies, Commented Apr 13, 2018 at 2:26

Jonas Staudenmeir · Accepted Answer · 2018-04-13 12:50:18Z

1

This query works with pagination:

Invoice::where('total_amount', '<=', function($query) {
    $query->selectRaw('SUM(amount_received)')
        ->from('invoice_payments')
        ->where('invoice_id', DB::raw('invoices.id'));
})->paginate();

answered Apr 13, 2018 at 12:50

Jonas Staudenmeir

26.1k7 gold badges70 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel query for multiple relationships

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related