php - Laravel 查询计数总行数

Question

我有这个查询:

$counts = DB::table('projects')
    ->join('prject_links', 'prject_links.project_id', '=', 'projects.id')
    ->join('links', 'links.id', '=', 'prject_links.link_id')
    ->join('segments', 'segments.id', '=', 'links.segment_id')
    ->join('hthree_regions', 'hthree_regions.id', '=', 'segments.hthree_id')
    ->join('areas', 'areas.id', '=', 'hthree_regions.area_id')
    ->join('zone_regions', 'zone_regions.id', '=', 'areas.zone_id')
    ->select(
        'zone_regions.id as regions',
        'areas.id as provinces',
        'hthree_regions.id as cities',
        'segments.id as segments'
    )
    ->get();

我希望得到的结果是这样的:

counts => [
  regions => 20,
  provinces => 10,
  cities => 7,
  segments => 5
];

我目前得到的是令人困惑的结果，例如:



关于如何解决我的查询的任何想法？

Answer 1

使用 groupBy和 count(distinct field)获取每个项目中字段的计数:

->selectRaw(
        "CONCAT('project ', projects.id) as project, 
         COUNT(DISTINCT zone_regions.id) as regions,
         COUNT(DISTINCT areas.id) as provinces,
         COUNT(DISTINCT hthree_regions.id) as cities,
         COUNT(DISTINCT segments.id) as segments"
    )
->groupBy('projects.id')
->get();