Laravel query where field is less than sum of another field in related table (polymorphic relationship)

In Laravel trying to query a table for invoices (table name: invoices) where the invoice total (field name: net_total) is greater than the sum of payments made for that invoice. It is set as a polymorphic relationship.

Invoice Table:

public function transactions(){
    return $this->morphMany(TransactionAllocation::class, 'doc');
}

Payment Table:

public function doc()
{
    return $this->morphTo();
}

I tried the query which does not seem to work.

Invoice::with('transactions')
        ->where('net_total', '>' ,'transactions.amount')
        ->get();

I tried the following as per an answer below which gives me sum of relationship but the where condition fails

Invoice::withSum('transactions', 'amount')
                    ->where('net_total','>','transactions_sum_amount')
                    ->get();

Screenshot of result – https://prnt.sc/1favj7u The second array shouldn’t have comes as it fails where condition.

Any advice on what am I missing?

Answer

Since you will have many transactions as you said, that is the part missing in the query.

You can try something with withSum for example

InvoiceTest::withSum('transactions', 'amount')
    ->havingRaw('transactions_sum_amount < invoices.net_total')
    ->get();

Advertisement

Answer