Error searching the database with cakephp-2

Question

I wonder what’s wrong with my consultation. I am debugging it, and is bringing all the results of the Registrohorario model and not just one result per user, ie the parts of "Fields", "group" and "order" are not being considered.

$us['joins'] =  array(
array(
    'table' => 'registro_horarios',
    'alias' => 'RegistroHorario',
    'type'  => 'LEFT',
    'fields' => array(
        '(SELECT cliente_id 
            FROM registro_horarios m
            WHERE m.user_id = registro_horarios.user_id
            ORDER BY data_fim DESC LIMIT 1) as cliente_id',
        'RegistroHorario.user_id'
    ),
    'conditions' => array(
        'User.id = RegistroHorario.id'
    ),
    'group' => 'RegistroHorario.user_id',
    'order' => 'RegistroHorario.MAX(data_fim) DESC'
));

$us['fields'] = array(
                'User.id'
);

$this->loadModel('User');

$usuarios = $this->User->find('all', $us);

Answer 1

I used an alternative solution to what I wanted to do, it’s ugly but it solves the problem:

public function qtdColaboradores(){
    $sql = 'SELECT (SELECT cliente_id
      from registro_horarios m
      where m.user_id = registro_horarios.user_id
      order by data_fim DESC LIMIT 1) as cliente_id,
      user_id
      FROM intranet.registro_horarios
      group by user_id
      order by MAX(data_fim) desc;';
    return $this->query($sql);
  }