如何使用laravel計算表關系的每一行ID？

所以我的資料庫中有 5 個帶有pivot的表，Countries, Cities, Shops, Users, shop_employee它們的關系是這樣的

Countries  | Cities      | Shops     | shop_employees| Users 
 id        | country_id  | city_id   |  shop_id      | id
                                        employee_id

我的目標是我想統計全國的每個城市、商店、員工

這是我的控制器

class CountryController extends Controller
{
    public function country(){
       $countries = Country::with(['cities.shops.employees'])->get();
       return view('country',['countries'=>$countries]);
    }
}

這是我的模范國家

class Country extends Model
{
    public function cities(){
        return $this->hasMany(City::class);
    }
}

這是我的模范城市

class City extends Model
{
    public function shops(){
        return $this->hasMany(Shop::class);
    }
}

這是我的模特店

class Shop extends Model
{
    public function employees(){
        return $this->belongsToMany(User::class,'shop_employees','shop_id','employee_id');
    }
}

這是我的看法

        <table class="table-fixed">
                <thead>
                  <tr>
                    <th class="py-2 px-2 border border-gray-300">Country</th>
                    <th class="py-2 px-2 border border-gray-300">City</th>
                    <th class="py-2 px-2 border border-gray-300">Shop</th>
                  </tr>
                </thead>
                <tbody>
                @foreach ($countries as $country)
                <tr >
                    <td class="py-2 px-2 border border-gray-300">{{ $country->name }}</td>
                    <td class="py-2 px-2 border border-gray-300">{{ $country->cities->count() }}</td>
                    <td class="py-2 px-2 border border-gray-300"></td>
                  </tr>
                @endforeach
                </tbody>
              </table>

我試過這段代碼，它作業得很好，城市正在計數

{{ $country->cities->count() }}

輸出，在這個輸出中我想顯示商店和員工的數量

但是當我嘗試這段代碼時，它給了我一個錯誤

  $country->cities->shops->count() or
  $country->cities->shops->employees->count() 

錯誤：Property [shops] does not exist on this collection instance

uj5u.com熱心網友回復：

您可以嘗試使用查詢本身獲取聚合

class CountryController extends Controller
{
    public function country(){
        $countries = Country::with([
            'cities' => fn($query) => $query->withCount('shops'),
            'cities.shops' => fn($query) => $query->withCount('employees'),
            'cities.shops.employees'
        ])
       ->withCount('cities')
       ->get();

       return view('country',['countries'=>$countries]);
    }
}

然后您可以訪問聚合值

//Get the count of cities under the country
$country->cities_count; 

foreach($countries as $country) {
    $noOfCities = $country->cities_count;

    $noOfShops = $country->cities->sum('shops_count');

    $noOfEmployees = $country->cities->reduce(
        fn($carry, $city) => $carry   $city->shops->sum('employees_count')
    );
}

Laravel Docs - 雄辯的關系 - 聚合相關模型

uj5u.com熱心網友回復：

您可以通過使用addSelect方法使用子查詢并傳遞閉包來實作這一點

Country::withCount('cities as city_count')
  ->addSelect([
    'shop_count' => Shop::selectRaw('count(*)')
    ->whereIn(
      'city_id',
       City::select('id')->whereColumn('country_id','countries.id'))
    ->limit(1), 
  ])
  ->addSelect([
    'employee_count' => DB::table('shop_employees')->selectRaw('count(*)')
    ->whereIn(
    'shop_id',
     Shop::select('id')
         ->whereIn(
           'city_id',
            City::select('id')->whereColumn('country_id','countries.id')
          )
     )->limit(1), 
  ])
  ->get();

標籤：拉拉维尔 laravel-5 雄辩 laravel-8 laravel 查询生成器

上一篇：下拉串列問題

下一篇：用雄辯的方式查詢里面