Error in database query with cakephp-2

Question

Error in database query with cakephp-2

Navigation

#1 by (0 votes)

0

I'd like to know what's wrong with my query. I'm debugging it, and it's bringing all the results of the ModelHorario template and not just one result per user, ie the 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);

cakephp-2

asked by anonymous 24.02.2015 / 17:02

1 answer

OracleException is not triggered! Remove () from Entity Framework 6 only works when re-creating the connection

score 0 · Accepted Answer

I used a workaround for what I wanted to do, it's ugly but 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);
  }