1
\$\begingroup\$

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?

\$\endgroup\$
3
  • \$\begingroup\$ I think you're bettter off querying the grouped counts directly from the database \$\endgroup\$
    – IGP
    Commented Dec 21, 2021 at 16:29
  • \$\begingroup\$ How to querying the grouped counts directly frrom the adatabase? \$\endgroup\$ Commented Dec 21, 2021 at 16:50
  • \$\begingroup\$ using count() and group by in a sql query. \$\endgroup\$
    – IGP
    Commented Dec 21, 2021 at 17:12

1 Answer 1

4
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.