[FIXED] Simple Count and Sort on Association Cakephp 3

Issue

I have students table and results table. Student has many results. A result is associated with only one student. So please what I'm trying to achieve is something like this:

$this->Students
    ->find('all')
    ->contain('Results')
    ->order('by count of results each student has' => 'asc');

Any help would be greatly appreciated.

Solution

Try this

$query = $this->Students->find()
    $query->select(['total_result'=> $query->func()->count('Results.id')])
    ->autoFields(true)
    ->contain('Results')
    ->leftJoinWith('Results')
    ->group(['Students.id'])
    ->order(['total_result'=>'ASC']);

debug($query->all());

More check Here

Answered By - tarikul05