I have three tables which are users, loans, statuses
The relationship is like this:
A user can have many loans. a loan has many status steps. in the statuses table I have a column called status, basically it telsl this step yes, no. pending sort of situation.
the table structure look like this:
users table
->id
->...
loans table
->id
->...
->user_id (it is the foreign key ->references('id')->on('users');
statuses table
->id
->...
->status (can be "yes", "no", "pending")
->...
->loan_id (it is the foreign key ->references('id')->on('loans');
the models look like this:
in the User model :
public function loans(){
return $this->belongsToMany('App\Loan');
}
in the Loan model:
public function users(){
return $this->belongsToMany('App\User');
}
public function statuses(){
return $this->hasMany('App\Status');
}
in the Status model:
public function loan(){
return $this->belongsTo('App\Loan');
}
My question is how to get the status yes number for each user. say I have five users, each user have multiple loans. each loan have, say 20 steps. but different loan many have different yes steps . I would like to use laravel eloquent ORM to get a array tell me each user get how many yes at certain time. So I would be able to loop through this array in my front end blade file to display users progress. thanks!
Laravel's Collection, which you get when you use Eloquent, is great for this kind of operation. Say you want to get the 'yes' statuses of one user:
//yesStatuses is itself a Collection, but it can be used in a foreach like an array
$yesStatuses = $user->loans
->map(function ($loan) {
return $loan->statuses;
})
->filter(function ($status) {
return $status->status === 'yes';
});
//Number of statuses === 'yes'
$yesStatuses->count();
//If you need it as a regular array
$yesStatuses->toArray();
When you query your users table you should take care of loading your loans and statuses eagerly, otherwise you'll be performing many queries for this operation. Something like this:
$users = App\User::with('loans.statuses')->get();
Thanks for your help, SrThompson!
I think map() function in the collection really helps. since my goal is to loop through the users to show each user's attributes plus count the "yes" status for that user. So I added my 'statuscount' into the user collection as:
$user['statuscount']=$yesStatusesCount;
my working code block like this:
$users=User::with('loans')->with('loans.statuses')
->map(function ($user){
$yesStatuses = $user->loans->map(function ($loan) {
return $loan->statuses->where('status','yes')->count();
});
$yesStatusesCount=array_sum($yesStatuses->toArray());
$user['statuscount']=$yesStatusesCount;
return $user;
});
Then, in my balde file, I would be able to display the number of "yes" status of the user in the @foreach loop. Thanks again!