CakePHP查询构建器LEFT OUTER JOIN无法生成正确SQL格式求助

首先明确原生SQL的核心逻辑：

• 子查询从e_products中筛选每个oc_id对应的非空最新itc_hs_code（按created降序排序）
• 主查询关联oc、子查询结果与schemes表，按scheme_id和itc_hs_code分组汇总epinvoicevalueusd

现有代码的问题 #

• 子查询未正确关联到主查询
• Join语法错误，group不是Join的合法参数
• 使用contain加载关联表不如直接Join高效
• Group by和Order by缺少itc_hs_code，不符合原生逻辑
• 字段别名与关联表引用不匹配

正确的查询构建器代码 #

1. 构建子查询（获取每个oc_id的最新非空itc_hs_code）

$latestProductQuery = $this->EProducts
    ->find()
    ->select(['oc_id', 'itc_hs_code'])
    ->where(['itc_hs_code IS NOT NULL'])
    ->group('oc_id')
    ->order(['oc_id' => 'ASC', 'created' => 'DESC']);

2. 主查询（关联子查询、Schemes表并汇总数据）

$ocData = $this->Oc
    ->find()
    ->select([
        'scheme_name' => 'Schemes.name',
        'itc_hs_code' => 'most_recent_export_product.itc_hs_code',
        'total_epinvoicevalueusd' => 'SUM(Oc.epinvoicevalueusd)'
    ])
    // 关联子查询（对应原生的most_recent_export_product）
    ->join([
        'table' => $latestProductQuery,
        'alias' => 'most_recent_export_product',
        'type' => 'INNER',
        'conditions' => 'most_recent_export_product.oc_id = Oc.id'
    ])
    // 关联Schemes表
    ->join([
        'table' => 'schemes',
        'alias' => 'Schemes',
        'type' => 'INNER',
        'conditions' => 'Schemes.id = Oc.scheme_id'
    ])
    ->where([
        'Oc.application_status_id' => 8,
        'Oc.certificate_issue_date >=' => '2022-09-28'
    ])
    ->group(['Oc.scheme_id', 'most_recent_export_product.itc_hs_code'])
    ->order(['Oc.scheme_id' => 'ASC', 'most_recent_export_product.itc_hs_code' => 'ASC'])
    ->all()
    ->toArray();

优化建议（确保取到真正的最新记录） #

原生SQL中group by oc_id+order by created desc的写法，在部分数据库中无法保证取到每个oc_id的最新itc_hs_code。推荐用窗口函数实现更准确的筛选：

$latestProductQuery = $this->EProducts
    ->find()
    ->select(['oc_id', 'itc_hs_code'])
    ->where(['itc_hs_code IS NOT NULL'])
    ->select(function ($q) {
        return [
            'row_num' => $q->func()->rowNumber()->over([
                'partition' => 'oc_id',
                'order' => ['created' => 'DESC']
            ])
        ];
    })
    ->having(['row_num' => 1]);

这个子查询会给每个oc_id下的记录按created降序编号，只保留编号为1的最新记录。

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