Count then Sum a second degree relation in laravel

问题:

While referring to @JarekTkaczyk's method, I am trying to count all people in a country.

1. A City hasMany People
2. A Country hasMany Cities

using hasManyThrough in Country, like so :

public function peopleCount(){
    return $this->hasManyThrough('App\Person', 'App\City')
        ->selectRaw('city_id, count(*) as count')
        ->groupBy('city_id');
}

I can access the count of People in each city. Yet it returns more than just the two fields city_id and count ! the count is correct, but the rest of the data is not something I'd want there. Here is an example : http://i.imgur.com/o1fyvEy.png

1. How do I make the query drop the other columns ?
2. How do I go further than counting students in each class, by summing all the counts into one value ?

Edit : more details on point two :

When I use the new made relation peopleCount to count all people in a country, it does so by counting all people in cities that belong to the country, thus returning a collection of counts that corresponds to each city, example in the following :

>>> $country = App\Country::with('peopleCount')->find(1)
=> <App\Country> {
       id: "1",
       peopleCount: <Illuminate\Database\Eloquent\Collection> [
           <App\Person> {
               city_id: "1",
               count: "3", //<-------
               id: "1",
               country_id: "1"
           },
           <App\Person> {
               city_id: "2",
               count: "5", //<-------
               id: "4",
               country_id: "1"
           },
           <App\Person> {
               city_id: "3",
               count: "8", //<-------
               id: "9",
               country_id: "1"
           }
       ]
   }

To get the sum of counts I do it PHP side rather than database side like so : $country->peopleCount->sum('count')

So I would rather do it all inside the query than doing it php-side.

回答1:

as per @Jarek Tkaczyk's response all i had to do was to country_id in the groupby clause