[FIXED] How to order a CakePHP query by an aggregate function on an associated table's field?

Issue

I want to order by an aggregate function on an associated table's field, but when I debug the SQL query being executed, the associated table, PersonaHistory doesn't even get JOINed, and of course I don't get any results.
Is there a way to achieve this?
Can I force a table to be joined in the query?

$query = $this->Personas->find('all')
    ->contain(['PersonaHistory'])
    ->order(['MAX(PersonaHistory.updated)' => 'ASC'])
    ->group('PersonaHistory.persona_id');  

DB: Personas has many PersonaHistory

Solution

It looks as if Personas is related to PersonaHistory via hasMany. Only hasOne and belongsTo associations produce a JOIN statement.

A workaround is to rewrite your query as:

$query = $this->PersonaHistory->find('all')
    ->contain(['Personas'])
    ->order(['MAX(PersonaHistory.updated)' => 'ASC'])
    ->group('PersonaHistory.persona_id');  

Answered By - Inigo Flores