Cakephp: Paging with manual query

Asked

Viewed 292 times

1

The The Cakephp Manual suggests a change in default pagination for custom queries cases and in some cases my project has worked perfectly. But I have a situation where I have several custom queries and I need to make the pagination in all of them (these 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 several 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');
php cakephp

  • It may explain your specific problem better, and why the manual method does not work in your case?

  • @bfavaretto, does not work because it does not meet my criteria. It has formatting, conditions and others that the basics of Cake does not provide me. As for explaining better, what don’t you understand? I need to do the paging in all custom queries and the Cake manual does not address this, I need to know how to do.

  • @Danilomiguel has how to post an example of one of these custom queries that you are doing and how you are working with paging so we can try to better understand what you have already done?

  • @Tafarelchicotti includes in the body of the question an example of query. As for paging, I’m not doing anything yet because besides the example query, I have at least 6 other different.

  • Which version of the cake you are using?

  • @Tafarelchicotti, use the 2.x.

  • The @gildonei response was perfect

  • 1

    Yeah, it took me a while to get back here :) So Danilo, as you saw in @gildonei’s reply, can avoid custom paging by forcing joins or using Containable behavior. Still, in certain cases it may be necessary to use this.

Show 3 more comments

2 answers

4


@Danilomiguel For this query of yours, you don’t need to manually mount, you can create virtualFields, or use in your find "field" itself and use the standard Cakephp find method

As I don’t know the relationship between your tables, I will use paginate with joins, but in case your relationships are of type Trading hasOne with Client, Situation and User (consultant) you can use the Containable behavior that is more beautiful and with better result, and exchange 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');
?>

  • Excellent solution, @gildonei. I will test here and confirm the result, but I believe it will suit me perfectly. Thank you!

  • Perfect, using the find() how it should be used, do not give +2 pq has no how. : D

0

Here’s an example of code that might be useful to you if in other situations you can’t use the gildonei example.

//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 up to you.

Browser other questions tagged php cakephp

You are not signed in. Login or sign up in order to post.