Query to get summary of regional model with count of contracts

Question

I have 3 tables:

• regional with Regional Model (id,kode)
• status_kontrak with StatusKontrak Model (id,status,user_id,...,...)
• users with User Model (id, regional_id).

My purpose is to get a collection or array from StatusKontrak to count how many StatusKontrak with status of active, expired, and null of each user respectively to its regional.

This is how I try to accomplish that.

public function buildSummaryByRegional()
{
 $regional = Regional::has('am')->with('am.regional','am.statusKontrak','am.activeStatusKontrak','am.expiredStatusKontrak','am.nullStatusKontrak')->get();
 $summaryRegional = [];
 foreach($regional as $r){
 //$r->am->loadCount('statusKontrak','activeStatusKontrak','expiredStatusKontrak','nullStatusKontrak');
 $am = $r->am;
 $in_prefix=[];
 foreach($am as $a){
 $parts = explode("@", $a->email);
 $prefix = $parts[0];
 $in_prefix[] = $prefix; 
 $a->prefix = $prefix; 
 $account = $a->statusKontrak->count();
 $active = $a->activeStatusKontrak->count();
 $expired = $a->expiredStatusKontrak->count();
 $null = $a->nullStatusKontrak->count();
 $summaryRegional[$r->kode]['am'][] = [
 'am'=>$a,
 'account'=>$account,
 'active'=>$active,
 'expired'=>$expired,
 'null'=>$null
 ];
 }
 $summaryRegional[$r->kode]['account']=StatusKontrak::whereIn('am',$in_prefix)->count(); 
 $summaryRegional[$r->kode]['active'] = StatusKontrak::whereIn('am',$in_prefix)->where('status','active')->count();
 $summaryRegional[$r->kode]['expired'] = StatusKontrak::whereIn('am',$in_prefix)->where('status','expired')->count();
 $summaryRegional[$r->kode]['null'] = StatusKontrak::whereIn('am',$in_prefix)->where('status','NULL')->count();
 }
 return $summaryRegional;
}

It works fine but I know it's not a best practice. How can I optimize this to a simpler way, perhaps to avoid the n+1 query problem?

• IGP: I think you're bettter off querying the grouped counts directly from the database
• Matius Nugroho Aryanto: How to querying the grouped counts directly frrom the adatabase?
• IGP: using count() and group by in a sql query.

Answer 1

You can use the withCount() method to get the count of child relationships.

Such as: user::withCount['getPosts']->find(1);
Where getPost() is a relationship defined in the user model.