ardi gunawan ardi gunawan - 2 months ago 22
MySQL Question

Use Distinct with join table Mysql laravel 5

I have a task from my boss to display data from mysql using laravel 5.
He give me this query for SQL

SELECT DISTINCT loans.loan_number, borrower.brate, loans.loint, loan_offer.amount,
loans.lterm,SUBSTRING(repayment_lender.repay_due_date,4,2)
FROM loans, loan_offer, borrower, repayment_lender,LENDER
WHERE loans.id = loan_offer.loan_id
AND loans.borrower_id = borrower.id
AND repayment_lender.loans_id = loans.id
AND lender.id = loan_offer.lender_id
AND lender.id = 3


if the query is executed it will look like this Like this

I used this code in my controller

$data_loanoffers = DB::table('loans')->join('loans', 'loans.id', '=', 'repayment_lender.loans_id')
->join('loans', 'loans.id', '=', 'loan_offer.loan_id')
->join('borrower', 'borrower.id', '=', 'loans.borrower_id')
->join('loan_offer', 'loan_offer.lender_id', '=', 'lender.id')
->where('lender.id',$l_id)->distinct()->get();
return view('account.investor_portfolio',[
'data_loanoffers' => $data_loanoffers,
]);


And use this code in view to display data

@foreach($data_loanoffers as $loans)
<tr>
</td>
<td>{{$loans->loan_number}}</div>
<td> {{$borrower->brate}} </div>
<td> {{$loans->loint}} </div>
<td> {{$loan_offer->amount}}</div>
<td> {{$loans->lterm}}</div>
<td> ?? </div>
</tr>

@endforeach


Display eror in front end LIKE THIS

I'm newbie in laravel 5. and I do not know where the error of my code. maybe master here can help me?
Thanks in advance.

Answer

I just solve my solution by using query builder from laravel

 $data_loanoffers        =  DB::select( DB::raw("SELECT DISTINCT loans.loan_number, borrower.brate, loans.loint, loan_offer.amount,loans.lterm,SUBSTRING(repayment_lender.repay_due_date,4,2) as DueDate
                                    FROM loans, loan_offer, borrower, repayment_lender,LENDER
                                    WHERE loans.id = loan_offer.loan_id
                                    AND loans.borrower_id = borrower.id
                                    AND repayment_lender.loans_id = loans.id
                                    AND lender.id = loan_offer.lender_id
                                    AND lender.id = '$l_id' ") );