Subquery of two related tables in laravel-CodePudding

I want to write the following query in Laravel 5.

SELECT *
FROM loans
WHERE loanAmount > (SELECT IFNULL(SUM(paidLoanAmount), 0)
                    FROM paid_loans WHERE loanId = loans.id);

I have tried the following, but subquery of PaidLoan seems not work due to loans.id or something else I don't notice.

Loan::where(
    'loanAmount', 
    '<=', 
    PaidLoan::where('paid_loans.loanId', 'loans.id')
        ->get()
        ->sum('paidLoanAmount')
)->get();

CodePudding user response：

You can use a leftJoin on the loan and just add a normal condition.

$payedLoans = Loan::leftJoin('paid_loans', 'paid_loans.loanId', '=', 'loans.id')
    ->where('loanAmount', '<=', \DB::raw("IFNULL(sum('paid_loans.paidLoanAmount'), 0)"))
    ->groupBy('loans.id')
    ->get();

CodePudding user response：

You can use whereRaw

 Loan::whereRaw("loanAmount > DB::raw('SELECT IFNULL(SUM(paidLoanAmount), 0)
                        FROM paid_loans WHERE loanId = loans.id')")->get();