Laravel count has many through without n 1-CodePudding

I have 4 models that are relevant.

Company

Location

Customer

ThirdPartyLinkClick

Company.php

public function locations() {
        return $this->hasMany('App\Location');
    }

Location.php

public function customers() {
   return $this->hasMany('App\Customer');
}

public function linkClicks() {
   return $this->hasManyThrough('App\ThirdPartyLinkClick', 'App\Customer');
}

Customer.php

public function linkClicks() {
   return $this->hasMany('App\ThirdPartyLinkClick');
}

There is no issue when acquiring the count of link clicks for all customers when on a single Location.

I can simply do: $this->linkClicks->count(); which creates a query where it does a WHERE IN (1,2,3,4, etc) query

However, on a Company page, I want to also get this count, but avoid an n 1

Right now, my model method on Company.php is

public function getTotalClicksToReviewSites() {
  $locations = $this->locations;

  $clicks = 0;
  foreach ($locations as $location) {
    $clicks  = $location->linkClicks->count();
  }

  return $clicks;
      
}

This creates duplicate queries where it checks location_id on each query. There will be a query for every location rather than checking a group of id's in a WHERE IN statement.

How can I do an eloquent query that will use a single query to gather this data? I only need the count.

CodePudding user response：

You need to use eager loading.

$companies = Company::with('locations', 'locations.linkClicks')->get();

This prevents n 1 query problem, so, to get te total of linkClicks for each company youur function will work or simply you can do.

$companies = Company::with('locations', 'locations.linkClicks')
    ->get()
    ->map(function ($company) {
        $company->linkClicksCount = $company->locations->sum(function ($location) {
            return $location->linkClicks->count();
        });
        
        return $company;
    });

The output should be something like this (a new property linkClicksCount added on each company).

[
    {"id": 1, "name": "Company 1", "linkClicksCount": 9, "locations": [], ...},
    {"id": 2, "name": "Company 2", "linkClicksCount": 3, "locations": [], ...},
    ...
]