How to get last record of duplicate data using group by in Laravel SQL

I want to retrieve last record of multiple row of same id using GroupBy in my laravel project.

table purchases

query in laravel controller

DB::table('purchase_transactions')
                        ->where('due_amount','>', 0)
                        ->groupBy('purchases_id')
                        ->orderBy('created_at', 'desc')
                        ->get(['purchases_id', DB::raw('MAX(id) as id')]);

output

But Only id no 3 will be the output cause only id no 3 purchases id has due amount.

How to solve this? Anybody Help please?

Answer

Looking at your requirement, you can add a simple havingRaw condition where the sum of due amount is greater or equal to the paid amount till now.

DB::table('purchase_transactions')
    ->groupBy('purchases_id')
    ->havingRaw('sum(due_amount) >= sum(paid_amount)')
    ->orderBy('created_at', 'desc')
    ->get(['purchases_id', DB::raw('MAX(id) as id')]);

Note: The above query assumes that if total paid amount is greater than the total due amount, then all due is paid off and that the rows are maintained in its mathematical uniformity for each purchase_id.

If total bill amount/net payable amount is in another table then need to inner join that table with this query and little change in havingRaw() function. See the below query

DB::table('purchase_transactions')
    ->join('purchases', 'purchases.purchases_id', '=', 'purchase_transactions.purchases_id')
    ->groupBy('purchase_transactions.purchases_id')
    ->havingRaw('sum(purchases.net_payable_amount) > sum(purchase_transactions.paid_amount)')
    ->orderBy('purchase_transactions.updated_at', 'desc')
    ->get(['purchase_transactions.purchases_id as purchases_id', DB::raw('MAX(purchase_transactions.id) as id')]);