CodeIgniter 4多表连接汇总多列结果错误的解决方法

问题场景 #

第一次使用CodeIgniter 4开发项目，需制作汇总页面对project、leading、man_hours三张表的多列数据求和统计，表结构如下：

project表 #

leading表 #

man_hours表 #

编写的代码如下：

ProjectModel代码

public function getSummary()
{
    $this->join('leading', 'leading.id_project=project.id', 'LEFT');
    $this->join('man_hours', 'man_hours.id_project=project.id', 'LEFT');
    $this->select('sum(permit) as permit, sum(job) as job');
    $this->select('sum(sum_manhours) as sum_manhours');
    $this->select('project.id, project.project_name');
    $this->groupBy('project.id');
    $result = $this->findAll();

    return $result;
}

Project控制器代码

public function manhours(){
    $data = [
        'title' => 'Summary',
        'project' => $this->projectModel->getSummary()
    ];

    return view('summary/view-summary-manhours', $data);
}

得到错误结果：

可见project 1的permit应为4、sum_manhours应为6，实际结果翻倍。

问题原因 #

直接同时关联leading和man_hours表会产生笛卡尔积：project 1在leading表中有2条记录，在man_hours表中有2条记录，关联后会生成2*2=4条记录。求和时，leading表的每条记录会被重复计算2次，man_hours表的每条记录也会被重复计算2次，最终导致结果翻倍。

解决方案 #

先分别对leading和man_hours表按id_project聚合求和，得到每个项目的单独汇总结果，再将这些汇总结果与project表关联，避免笛卡尔积的产生。

修改后的ProjectModel代码：

public function getSummary()
{
    // 子查询：计算每个项目的leading数据汇总
    $leadingSubquery = $this->db->table('leading')
        ->select('id_project, SUM(permit) as total_permit, SUM(job) as total_job')
        ->groupBy('id_project')
        ->getCompiledSelect();
    
    // 子查询：计算每个项目的man_hours数据汇总
    $manHoursSubquery = $this->db->table('man_hours')
        ->select('id_project, SUM(sum_manhours) as total_sum_manhours')
        ->groupBy('id_project')
        ->getCompiledSelect();
    
    // 关联project表与两个子查询的汇总结果
    $this->select('project.id, project.project_name');
    // 使用COALESCE处理无对应数据的项目，将NULL替换为0
    $this->select('COALESCE(ls.total_permit, 0) as permit');
    $this->select('COALESCE(ls.total_job, 0) as job');
    $this->select('COALESCE(mhs.total_sum_manhours, 0) as sum_manhours');
    $this->join("($leadingSubquery) ls", 'ls.id_project = project.id', 'LEFT');
    $this->join("($manHoursSubquery) mhs", 'mhs.id_project = project.id', 'LEFT');
    
    return $this->findAll();
}

验证结果 #

修改后查询得到的正确结果：

内容的提问来源于stack exchange，提问作者Shannaz Wakid