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

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?

CodePudding user response：

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.