mardi 27 septembre 2016

How to do this in Laravel, sql with subquery

How can I make this query in Laravel Query Builder:

SELECT DISTINCT
loans.loan_number, 
users.email, 
loans.created_at, 
loan_purpose.lppos, 
loans.lnamt,
(SELECT sum(repayment_borrower.settle_amount + repayment_borrower.settle_admin + repayment_borrower.settle_penalty + repayment_borrower.settle_interest) FROM repayment_borrower  LEFT JOIN loans A ON(repayment_borrower.loans_id = A.id) WHERE A.id = loans.id AND repayment_borrower.status_id = '111' ) as 'settlement',
(SELECT sum(repayment_borrower.settle_amount + repayment_borrower.settle_admin + repayment_borrower.settle_penalty + repayment_borrower.settle_interest) FROM repayment_borrower  LEFT JOIN loans A ON(repayment_borrower.loans_id = A.id) WHERE A.id = loans.id AND repayment_borrower.status_id = '222' ) as 'Outstanding',
loans.lterm, 
loans.loint
FROM loans 
LEFT JOIN repayment_borrower ON (loans.id = repayment_borrower.loans_id)
JOIN loan_purpose ON(loan_purpose.lpose_id = loans.lpose)
JOIN borrower ON(loans.borrower_id = borrower.id)
JOIN users ON(borrower.user_id = users.id)

Thanks in advance! Cheers



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire