CakePHP Query Builder实现关联模型多SUM计算的优雅方案咨询

这个问题的核心是多表连接产生的笛卡尔积导致求和结果被重复计算——当你同时innerJoinWith和leftJoinWith两个关联表时，每个发票的商品记录会和收款记录两两组合，最终求和时相当于把每个商品金额重复计算了收款次数，收款金额重复计算了商品次数，结果自然翻倍。

要完美复刻你原生SQL的逻辑，用CakePHP Query Builder的正确方式是先创建两个独立的子查询分别计算商品总额和收款总额，再将它们与主表关联，具体代码如下：

步骤1：创建商品总额的子查询 #

// 构建InvoiceItems的分组求和子查询
$invoiceItemsSubquery = $this->Invoices->InvoiceItems->find()
    ->select([
        'invoice_id',
        'SumOfAmount' => function ($q) {
            return $q->func()->sum('invoice_qty * unit_price');
        }
    ])
    ->group('invoice_id');

步骤2：创建收款总额的子查询 #

// 构建PaymentReceipts的分组求和子查询
$paymentsSubquery = $this->Invoices->PaymentReceipts->find()
    ->select([
        'invoice_id',
        'SumOfPaymentAmount' => function ($q) {
            return $q->func()->sum('receipt_amount');
        }
    ])
    ->group('invoice_id');

步骤3：主查询关联子查询 #

// 主查询关联两个子查询，和原生SQL逻辑完全一致
$finalQuery = $this->Invoices->find()
    ->select([
        'Invoices.id',
        'Invoices.invoice_name',
        'SumOfAmount' => 'InvoiceItemSum.SumOfAmount',
        'SumOfPaymentAmount' => 'PaymentSum.SumOfPaymentAmount'
    ])
    // 内连接商品总额子查询（确保只有有商品的发票被返回）
    ->innerJoin(
        [$invoiceItemsSubquery => 'InvoiceItemSum'],
        ['InvoiceItemSum.invoice_id = Invoices.id']
    )
    // 左连接收款总额子查询（允许没有收款的发票返回NULL）
    ->leftJoin(
        [$paymentsSubquery => 'PaymentSum'],
        ['PaymentSum.invoice_id = Invoices.id']
    )
    ->where(['Invoices.id' => 33]);

为什么这个写法能解决问题？ #

和你的原生SQL一样，子查询会先单独对每个发票的商品金额、收款金额做分组求和，得到每个发票唯一的一条汇总记录，再和主表关联时不会产生笛卡尔积，求和结果自然是准确的。

如果你想简化子查询的写法，也可以直接用$q->func()->sum()的简写形式：

// 简化后的子查询写法
$invoiceItemsSubquery = $this->Invoices->InvoiceItems->find()
    ->select([
        'invoice_id',
        'SumOfAmount' => $this->Invoices->InvoiceItems->query()->func()->sum('invoice_qty * unit_price')
    ])
    ->group('invoice_id');

这样生成的SQL和你手动写的原生SQL几乎完全一致，既符合CakePHP的查询规范，又能得到正确的结果。

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