Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Calid's avatar
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 :)

0 likes
0 replies

Please or to participate in this conversation.