This is the SQL query that I'm trying to replicate in CakePHP.
$sql = "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";
Essentially, ServiceLineEntries hasOne EscalationEntries and ServiceLines belongsTo ServiceLinentries.
The AuditLogs table is populated by the AuditStash behavior, thus contains audit data from many different tables in my app identified in the column source.
Here is my CakePHP code:
$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]);
Errors:
Column not found: 1054 Unknown column 'EscalationEntries.id' in 'on clause'
It doesn't work even if I specify the physical table name
'conditions' => [
"AuditLogs.primary_key = escalation_entries.id",
"AuditLogs.source = 'escalation_entries'"
],
Column not found: 1054 Unknown column 'escalation_entries.id' in 'on clause'
Edit #1: need to see columns from AuditLogs.
Edit #2: using the raw SQL, I can in theory massage the variable to make it easier to loop in the template:
$sql = "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";
$connection = ConnectionManager::get('default');
$results = $connection->execute($sql)->fetchAll('assoc');
$out = [];
foreach($results as $r)
{
$out[$r['name_en']][] = $r;
}