如何在CakePHP中复现指定SQL？关联AuditLogs报错排查

问题背景 #

需要复现以下SQL查询的CakePHP查询构建器写法：

select sl.name_en, l.*
from service_line_entries sle 
    inner join escalation_entries ee on ee.service_line_entry_id=sle.id
    inner join service_lines sl on sle.service_line_id=sl.id
    inner join audit_logs l on l.primary_key = ee.id and l.source='escalation_entries'
where sle.request_id=69060
order by sl.name_en asc, created asc

已定义的模型关联：

• ServiceLineEntries hasOne EscalationEntries
• ServiceLines belongsTo ServiceLineEntries
• AuditLogs表由AuditStash行为维护，通过source字段区分不同来源表

自行编写的代码：

$this->ServiceLineEntries = TableRegistry::getTableLocator()->get('ServiceLineEntries');

$sle = $this->ServiceLineEntries->find()
->contain(['EscalationEntries'])
->join([
    'table' => 'audit_logs',
    'alias' => 'AuditLogs',
    'type' => 'INNER',
    'conditions' => [
        "AuditLogs.primary_key = EscalationEntries.id", 
        "AuditLogs.source = 'escalation_entries'"
    ],
])
->where(['ServiceLineEntries.request_id'=>69060]);

执行后报错：

Column not found: 1054 Unknown column 'EscalationEntries.id' in 'on clause'

补充：需要获取AuditLogs的列数据；原始SQL可正常执行，希望用CakePHP查询构建器实现。

问题原因 #

contain()用于关联数据的预加载/懒加载，它不会将关联表加入主查询的JOIN子句，而是生成独立查询或在主查询后附加查询。因此在join()的条件中直接引用EscalationEntries的字段时，数据库找不到该表——因为它根本没被包含在主查询的JOIN里。

解决方案 #

方案一：显式JOIN所有关联表 #

直接通过join()方法将所有需要的表加入主查询，确保字段引用有效：

$this->ServiceLineEntries = TableRegistry::getTableLocator()->get('ServiceLineEntries');

$query = $this->ServiceLineEntries->find()
    ->select([
        'ServiceLines.name_en',
        'AuditLogs.*' // 可指定具体字段代替*，避免冗余
    ])
    ->join([
        'EscalationEntries' => [
            'table' => 'escalation_entries',
            'type' => 'INNER',
            'conditions' => 'EscalationEntries.service_line_entry_id = ServiceLineEntries.id'
        ],
        'ServiceLines' => [
            'table' => 'service_lines',
            'type' => 'INNER',
            'conditions' => 'ServiceLineEntries.service_line_id = ServiceLines.id'
        ],
        'AuditLogs' => [
            'table' => 'audit_logs',
            'type' => 'INNER',
            'conditions' => [
                'AuditLogs.primary_key = EscalationEntries.id',
                "AuditLogs.source = 'escalation_entries'"
            ]
        ]
    ])
    ->where(['ServiceLineEntries.request_id' => 69060])
    ->order([
        'ServiceLines.name_en' => 'ASC',
        'AuditLogs.created' => 'ASC' // 指定表别名，避免多表created字段歧义
    ]);

方案二：利用joinWith()复用模型关联 #

如果想复用已定义的模型关联，用joinWith()替代contain()，它会将关联表加入主查询的JOIN子句：

$this->ServiceLineEntries = TableRegistry::getTableLocator()->get('ServiceLineEntries');

$query = $this->ServiceLineEntries->find()
    ->select([
        'ServiceLines.name_en',
        'AuditLogs.*'
    ])
    ->joinWith('EscalationEntries') // 将EscalationEntries加入主查询JOIN
    ->join([
        'AuditLogs' => [
            'table' => 'audit_logs',
            'type' => 'INNER',
            'conditions' => [
                'AuditLogs.primary_key = EscalationEntries.id',
                "AuditLogs.source = 'escalation_entries'"
            ]
        ]
    ])
    ->joinWith('ServiceLines') // 同样用joinWith加入ServiceLines
    ->where(['ServiceLineEntries.request_id' => 69060])
    ->order([
        'ServiceLines.name_en' => 'ASC',
        'AuditLogs.created' => 'ASC'
    ]);

注意事项 #

• 尽量在select()中指定具体字段，避免*带来的冗余和字段冲突
• 排序、条件中涉及多表共有的字段时，必须指定表别名（如AuditLogs.created）
• 若AuditLogs需频繁和其他表关联，可考虑给AuditLogs模型添加动态关联规则，或封装行为简化查询

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