Laravel sum query using join and distinct

Question

I have 2 tables to query using join, namely loans and amortizations table.

Loans table has many amortizations, so loans.id is a reference to amortizations.loan_id in amortizations table.

Loan id:3 exist twice in amortizations table, each with a value=10,800.
Loan id:5 also exist twice in amortizations table, each with a value=11,100 and the same goes with Loan id:11 that also exist twice in amortizations table each with a value=5400.
When you add this up, the total is 54,600. But I wanted the ids to be distinct so that the values won't add twice. So the total would be equal to 27,300. My existing query below returns a 54,600 value instead of 27,300 even using distinct() in Laravel. How would I achieve this?

Controller query

$oneToThirtyDue = Amortization::distinct('amortizations.loan_id')
                ->join('loans', 'loans.id', '=', 'amortizations.loan_id')
                ->select('loans.loan_type',\DB::raw('SUM(loans.loan_balance) as total_val'))
                ->where('amortizations.payment_status',0)
                ->where('amortizations.schedule', '<', date('Y-m-d'))
                ->where('amortizations.past_due', '<=', 30)
                ->groupBy('loans.loan_type')
                ->orderBy('loans.loan_type')
                ->get();

Please help. Thanks a lot.

Answer 1

   $idsDue = Amortization::distinct()
                           ->select('loan_id')
                           ->where('payment_status',0)
                           ->where('schedule', '<', date('Y-m-d'))
                           ->where('past_due', '<=', 30)
                           ->get();

    $oneToThirtyDue = Loan::select('loan_type',\DB::raw('SUM(loan_balance) as total_val'))
                                  ->whereIn('id',$idsDue)
                                  ->groupBy('loan_type')
                                  ->orderBy('loan_type')
                                  ->get();

Answer 2

I believe this problem is that the max will be calculated for all rows in the group by before the distinct happens. Meanwhile distinct will run on the whole row, after the max so i does not help.

Therefor i believe a sub query can solve most of your troubles. The approach is to cut down the duplicate in the Amortization before joining. This will make the join be a 1 to 1, instead of it sometimes being 2-1 when you have duplicates. Reversing the join for ease and using a group by for avoiding duplicates and getting extra columns with max as they are not in the group by. Got a similar query working on local testing on some test tables, so this should be possible to achieve.

$subQuery = Amortization::select(
    'loan_id',
    DB::raw('max(amortizations.id) as id'),
    DB::raw('max(amortizations.payment_status) as payment_status'),
    DB::raw('max(amortizations.schedule) as schedule'),
    DB::raw('max(amortizations.past_due) as past_due')
)->groupBy('loan_id');

Loan::join(DB::raw("($subQuery->toSql()) as amortizations"), 'amortizations.loan_id', '=', 'loans.id')
    ->select('loans.loan_type',\DB::raw('SUM(loans.loan_balance) as total_val'))
    ->where('amortizations.payment_status',0)
    ->where('amortizations.schedule', '<', date('Y-m-d'))
    ->where('amortizations.past_due', '<=', 30)
    ->orderBy('loans.loan_type');

This has to be done with a DB::raw, but i can not see any better approach as of now, sub queries outside of select statements is a grey area in Laravel.