Mar 6, 2019
0
Level 3
Query model with calculations from two relation tables
I have 3 tables and 3 models for each : Invoice / Payment / Item
invoices : table
----------------------------------------------------------------
| id | user_id | discount_amount | due_at
----------------------------------------------------------------
items : table
-------------------------------------------------------
| id | invoice_id | description | price
-------------------------------------------------------
payments : table
---------------------------------------------------------
| id | invoice_id | amount | created_at
---------------------------------------------------------
What I want is to query the (Invoice) model using sum of item prices minus the sum of payments and discount amount ( i.e => selected invoices where ( sum(items.price) - sum(payments.amount) - discount_amount ) > some value ).
basically query which invoices are paid, not paid or partially paid
my current query for index look like this :
public function index() {
$invoices = Invoice::with('items', 'payments', 'user')->where('void', '!=', 1)->get()
}
Many Thanks :)
Please or to participate in this conversation.