Laravel Query with Function - Stack Overflow

admin管理员组

文章数量:1289497

I have this query:

$invoices = Vendor_invoice::where('vendor_id',$id)->where('status','!=',"Paid")->orderBy('due_date', "ASC")->get();

It gets all invoices that are not paid in full for a specific vendor. It's possible that payments have been applied to an invoice so I need to query the Invoice_payments table to get the sum of all payments applied to an invoice and do so within the same query in order to populate a table and include the amount due and not simply the invoice total.

I have this query:

$invoices = Vendor_invoice::where('vendor_id',$id)->where('status','!=',"Paid")->orderBy('due_date', "ASC")->get();

It gets all invoices that are not paid in full for a specific vendor. It's possible that payments have been applied to an invoice so I need to query the Invoice_payments table to get the sum of all payments applied to an invoice and do so within the same query in order to populate a table and include the amount due and not simply the invoice total.

• laravel

Share Improve this question Follow asked Feb 19 at 23:52 T.A.T.A. 64211 gold badge77 silver badges1717 bronze badges 1

• Please add the the relationship code that you are using. You can use withSum Method to get the sum – ManojKiran Commented Feb 20 at 7:01

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

you can use addSelect

$invoices = Vendor_invoice::addSelect([
            'amount_due' => Invoice_payments::selectRaw('sum(amount)')->whereColumn('num_invoice', 'Vendor_invoice.id')
        ])->where('vendor_id',$id)->where('status','!=',"Paid")->orderBy('due_date', "ASC")->get();

Since I do not know the entire table structure, I might be using a different FK, but here's a query that might help you.

$invoices = Vendor_invoice::where('vendor_id',$id)
    ->withSum('invoicePayments', 'price')
    ->where('status','!=',"Paid")
    ->orderBy('due_date', "ASC")
    ->get();

This assumes that your Vendor_invoice model implements the eloquent relation of Invoice_Payment model, and the sum of price column of invoice_payments table.

After applying the withSum query, you can access its value like this:

$invoices[0]->invoicePayments_sum_price

This can be found in Laravel's documentation.

Hopefully this helps.

本文标签： Laravel Query with FunctionStack Overflow