Codeigniter 4多表关联汇总多列结果错误的解决求助

问题原因 #

直接同时关联leading和man_hours两张表会产生笛卡尔积：对于project1，leading有2条记录，man_hours有2条记录，关联后会生成2×2=4条重复组合的记录。SUM函数会对这4条记录进行求和，导致结果被重复计算，最终出现翻倍（甚至更多倍）的错误。

解决方案 #

通过子查询分别计算各表的汇总数据，再将汇总结果与project表关联，避免笛卡尔积的产生。修改ProjectModel的getSummary方法如下：

public function getSummary()
{
    // 子查询1：计算每个项目的leading表汇总
    $leadingSubquery = $this->db->table('leading')
        ->select('id_project, SUM(permit) as permit, SUM(job) as job')
        ->groupBy('id_project')
        ->getCompiledSelect();

    // 子查询2：计算每个项目的man_hours表汇总
    $manHoursSubquery = $this->db->table('man_hours')
        ->select('id_project, SUM(sum_manhours) as sum_manhours')
        ->groupBy('id_project')
        ->getCompiledSelect();

    // 关联project表与两个子查询的结果
    $this->select('project.id, project.project_name')
         ->select('leading_sum.permit, leading_sum.job')
         ->select('man_hours_sum.sum_manhours')
         ->join("($leadingSubquery) as leading_sum", 'leading_sum.id_project = project.id', 'LEFT')
         ->join("($manHoursSubquery) as man_hours_sum", 'man_hours_sum.id_project = project.id', 'LEFT');

    return $this->findAll();
}

原理说明 #

1. 两个子查询分别对leading和man_hours按id_project分组求和，得到每个项目的独立汇总数据，每个项目仅返回1条记录。
2. 将这两个子查询的结果作为临时表，与project表进行关联，此时不会产生笛卡尔积，求和结果即为正确值。

预期结果 #

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