CODEWITHSUNDEEP
cakephpcakephp-3.0query-builder
Petruza
Petruza

Reputation: 12266

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

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

Upvotes: 0

Views: 852

Answers (2)

Inigo Flores
Inigo Flores

Reputation: 4469

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');

Upvotes: 2

Ch0c4
Ch0c4

Reputation: 26

your table name need to be plurialized and you can execute your order and group directly on your associated table field

<?php $query = $this->Personas->find('all')->contain([
    'PersonaHistories' => function ($q) {
        return $q->order(['Max(updated)' => 'ASC'])
                 ->group('persona_id');
    }]);

Upvotes: 0

Related Questions