Eloquent Many to Many Where Sum Equal to a Column Value

Question

I have Order and Invoice models which have many-to-many relationships connected using the OrderInvoice pivot. The tables have attributes:

order
-----
id
total

invoice
-------
id
total

order_invoice
-------------
order_id
invoice_id
subtotal

I want to get every Order which sum of its invoices subtotal (from the pivot) is less than the Order total. This includes Order which have no invoice at all. How can I achieve that using the eloquent query builder?

Answer 1

If i understood your question correctly then you can do the below.

$order=\App\Models\Order::query()->whereHas("invoice",function ($query){
        $query->havingRaw('orders.total>sum(order_invoice.subtotal)')->groupBy('order_id');
    })->orDoesntHave("invoice")
        ->get();

If you still have error then please post both order and invoice model code so we can see where it went wrong