CakePHP: Pagination with manual query

Navigation
1

The CakePHP manual suggests a change in the default paging for cases of custom queries and in some cases my project has worked perfectly. But I have a situation where I have several custom queries and I need to do the pagination in all of them (they are very specific reports, which the Cake standard does not meet).

In the case of the manual, it informs the change only for a specific case, but in these cases with multiple queries, how to proceed?

Example of a custom query I'm using: 

    $data = $this->Negociacao->query('SELECT
        Consultor.nome,
        Cliente.nome,
        Negociacao.id,DATE_FORMAT(Negociacao.created,"%d/%m/%Y") AS data,SUBSTR(Negociacao.empresa,1,15) AS empresa,SUBSTR(Negociacao.assessoria,1,15) AS assessoria,
        Situacao.nome,
        CASE WHEN Negociacao.negociacao_status_id != 3 THEN
        DATEDIFF(NOW(),Negociacao.created) ELSE NULL END
        AS dias,
        CASE WHEN Negociacao.negociacao_status_id != 3 AND DATEDIFF(NOW(),Negociacao.created) > 3 THEN "Y" ELSE "N" END AS atrasado
        FROM negociacoes Negociacao
        LEFT JOIN clientes Cliente
        ON Negociacao.cliente_id = Cliente.id
        LEFT JOIN negociacao_status Situacao
        ON Negociacao.negociacao_status_id = Situacao.id
        LEFT JOIN usuarios Consultor
        ON Negociacao.consultor_id = Consultor.id           
        WHERE Negociacao.consultor_id = '.$consultor.'
        ORDER BY data');
    
asked by anonymous 24.06.2015 / 21:21

2 answers

4

@DaniloMiguel For this query, you do not have to mount manually, you can create virtualFields, or use it in the "field" of your find and use the default method of find CakePHP

As I do not know the relationship between your tables, I will use paginate with joins, but if your relationships are HasOne ) you can use the Containable behavior that looks more beautiful and with better result, besides exchanging LEFT JOIN for INNER JOIN. 

<?php
$this->Negociacao->virtualFields = array(
    'dias' => 'CASE WHEN Negociacao.negociacao_status_id != 3 THEN DATEDIFF(NOW(),Negociacao.created) ELSE NULL END',
    'atrasado' => 'CASE WHEN Negociacao.negociacao_status_id != 3 AND DATEDIFF(NOW(),Negociacao.created) > 3 THEN "Y" ELSE "N" END'
);

$this->paginate = array(
    'fields' => array(
        'Negociacao.id', 
        'Negociacao.created',   // Sugiro formatar na view, na hora de apresentar os dados
        'Negociacao.empresa',   // Sugiro formatar na view, na hora de apresentar os dados
        'Negociacao.assessoria',// Sugiro formatar na view, na hora de apresentar os dados
        'Negociacao.dias',
        'Consultor.nome',
        'Cliente.nome'
    ),
    'conditions' => array('Negociacao.consultor_id' => $consultor),
    'joins' => array(
        array(
            'table' => 'clientes',
            'alias' => 'Situacao',
            'type' => 'LEFT',
            'conditions' => 'Negociacao.negociacao_status_id = Situacao.id'
        ),
        array(
            'table' => 'negociacao_status',
            'alias' => 'Cliente',
            'type' => 'LEFT',
            'conditions' => 'Negociacao.cliente_id = Cliente.id'
        ),
        array(
            'table' => 'usuarios',
            'alias' => 'Consultor',
            'type' => 'LEFT',
            'conditions' => 'Negociacao.consultor_id = Consultor.id'
        )
    ),
    'order' => array('Negociacao.data' => 'ASC'),
    'recursive' => -1
);

$this->paginate('Negociacao');
?>
    
24.06.2015 / 22:30
0

Here is an example of code that might be useful to you, if in other situations you can not use the gildonei exhange. 

//Verifica a quantidade de itens a ser exibido na tela
if (isset($_REQUEST['rowCount'])) {
   $rows = $_REQUEST['rowCount'];
}

// Indica qual a página atual, será usada para calcular qual a posição de offset e limit (skip,take) utilizados.
if (isset($_REQUEST['current'])) {
    $current = $_REQUEST['current'];
    $skip = ($current * $rows) - ($rows);
    $take = $rows;
} else {
    $skip = $current - 1;
    $take = $rows;
}

//Guardando a query string
$query = '....';

//Descobrindo a quantidade de itens ao todo
$qtde = $this->Model->('select count...');

// Retorna a quantidade de itens apenas necessários para construir a página
$result = $this->Model->query('...'."limit $take offset $skip");

And then the rest is with you.

    
24.06.2015 / 22:38
How to change the character encoding in phpmailer Failed to register a package created in Laravel 5.1