creating query from other tables that has the ID of the primary table in laravel

Question

Apology for the title. I'm really not sure how to name the title correctly base from my situation. I'm new in coding that's why I am not familiar with proper terminologies.

below are the tables I am working on right now.

I am displaying the details from loan_application table. I can able to include the loan_durations and users in my @foreach but I realized I need to include also the SUM of the AMOUNT from loan_interests table and the SUM of AMOUNT from LOAN PENALTIES which gives me an headache because I can't pull them out.

LOAN APPLICATION MODEL

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class LoanApplication extends Model
{
    public function user()
    {
        return $this->hasOne('App\User', 'id','user_id');
    }

    public function loanDuration()
    {
        return $this->hasOne('App\LoanDuration', 'id','loan_duration_id');
    }

    public function interest()
    {
        return $this->belongsTo('App\LoanInterest','loan_id', 'id');
    }    

    public function penalty()
    {
        return $this->belongsTo('App\LoanPenalty','loan_id', 'id');
    }    
}

LOAN INTEREST MODEL

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class LoanInterest extends Model
{
    public function loanInterest()
    {
        return $this->belongsTo('App\LoanApplication','loan_id', 'id');
    }    
}

LOAN PENALTY MODEL

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class LoanPenalty extends Model
{
    public function loanPenalties()
    {
        return $this->belongsTo('App\LoanApplication','loan_id', 'id');
    }    
}

for my controller

public function collectorMembers($id)
{
    $collectormember = CollectorMember::where('collector_id',$id)->get();

    return view('dashboard.collector-members.collector-borrowers-list', compact('collectormember'));
}

This gives this result

the CollectorMember gets the info on this table

Can you help me please? thanks a lot in advance!

Answer 1

There is a way to accomplish what you're after, but first I think you need to revisit your schema and relationships.

• Foreign key columns should be of the same type as the referenced column. For example loan_id on the loan_interests should be int(11) just like the id column on the loan_applications table
• I think you may be confusing relationship types. For example a User hasMany LoanApplications and a LoanApplication belongsTo a User. A LoanApplication hasMany LoanPenaltys and hasMany LoanInterests. A LoanPenalty and a LoanInterest both belongsTo a LoanApplication
• The user_id column on the loan_penalities table is redundant because a LoanPenalty belongsTo a LoanApplication and a LoanApplication belongsTo a User
• I'd recommend storing currency amounts in cents and using unsigned integers as the column type (e.g. for interest_amount)

Consider the following schema (some columns not shown):

Then consider the following models with relationships:

class User extends Model {
    public function loanApplications()
    {
        return $this->hasMany(LoanApplication::class);
    }

    public function collectors()
    {
        return $this->belongsToMany(Collector::class);
    }
}

class Collector extends Model {
    public function users()
    {
        return $this->belongsToMany(User::class);
    }
}

class LoanApplication extends Model {
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function loanDuration()
    {
        return $this->belongsTo(LoanDuration::class);
    }

    public function loanInterests()
    {
        return $this->hasMany(LoanInterest::class, 'loan_id');
    }

    public function loanPenalties()
    {
        return $this->hasMany(LoanPenalty::class, 'loan_id');
    }
}

class LoanDuration extends Model {
    public function loanApplications()
    {
        return $this->hasMany(LoanApplication::class);
    }
}

class LoanInterest extends Model {
    public function loanApplication() {
        return $this->belongsTo(LoanApplication::class, 'loan_id');
    }
}

class LoanPenalty extends Model {
    public function loanApplication()
    {
        return $this->belongsTo(LoanApplication::class, 'loan_id');
    }
}

Then to list all loan applications in a Resource Controller:

class LoanApplicationController extends Controller {
    public function index()
    {
        $loan_applications = LoanApplication
                                ::with(['user', 'loanInterests', 'loanPenalties'])
                                ->get();

        $loan_applications = $loan_applications->map(function ($loan_application) {
            $loan_application->loan_penalities_sum = $loan_application->loanPenalties->sum('penalty_amount_cents');
            $loan_application->loan_interests_sum = $loan_application->loanInterests->sum('interest_amount_cents');
            return $loan_application;
        });

        return view('dashboard.loan-applications.index', compact('loan_applications'));
    }
}

And in your dashboard.loan-applications.index blade template:

<table>
    <tr>
        <th>Username</td>
        <th>Total Interest</td>
        <th>Total Penalty</td>
    </tr>
    @foreach ($loan_applications as $loan_application)
        <tr>
            <td>{{$loan_application->user->username}}</td>
            <td>{{$loan_application->loan_interests_sum}}</td>
            <td>{{$loan_application->loan_penalties_sum}}</td>
        </tr>
    @endforeach
</table>

Note the above example does not include pagination; all resources are loaded at once.

The above example also assumes there should be a many-to-many relationship between collectors and users, but I would imagine a Collector should be related to the loan_applications table, not to a User.