CodeIgniter Forums
Change the paginate method - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Feature Requests (https://forum.codeigniter.com/forumdisplay.php?fid=29)
+--- Thread: Change the paginate method (/showthread.php?tid=73709)



Change the paginate method - videoproc - 05-25-2019

Hi! Since the manual numbering of pages in the method  $ pager->makeLinks ($page, $perPage, $total) is $total,
I propose to change the paginate method in the System/Model.php:

PHP Code:
public function paginate(int $perPage 20string $group 'default'int $page 0,bool return_with_total false)
    {
        
// Get the necessary parts.
        
$page $page >= $page : (ctype_digit($_GET['page'] ?? '') && $_GET['page'] > $_GET['page'] : 1);

        
$total $this->countAllResults(false);

        
// Store it in the Pager library so it can be
        // paginated in the views.
        
$pager       = \Config\Services::pager();
        
$this->pager $pager->store($group$page$perPage$total);

        
$offset = ($page 1) * $perPage;

        if(
return_with_total)return ['total'=>$total,'table' => $this->findAll($perPage$offset)]; // Add

        
return $this->findAll($perPage$offset);
    } 
This will avoid unnecessary duplicate requests.

Code:
2019-05-25T08:55:27.306774Z       33 Query    SELECT COUNT(*) AS `numrows`
FROM `customer`
WHERE `id` IN (1,2,3,4,5)
AND `name` LIKE '%test%'
2019-05-25T08:55:27.307196Z       33 Query    SELECT COUNT(*) AS `numrows`
FROM `customer`
WHERE `id` IN (1,2,3,4,5)
AND `name` LIKE '%test%'
2019-05-25T08:55:27.309970Z       33 Query    SELECT *
FROM `customer`
WHERE `id` IN (1,2,3,4,5)
AND `name` LIKE '%test%'
ORDER BY `id` ASC
LIMIT 10
2019-05-25T08:55:27.324738Z       33 Quit



RE: Change the paginate method - sv3tli0 - 05-29-2019

Paginate acts as findAll with auto applied limit + offset and prepared Pager..

I think that logically countAllResults(false) should preserve the count once it's done and just to return it instead making multiple selects further..
https://github.com/codeigniter4/CodeIgniter4/blob/develop/system/Database/BaseBuilder.php#L1566
Running that query multiple times in case $sql and $this->binds are the same is pointless specially if $reset is set to false..


RE: Change the paginate method - videoproc - 05-30-2019

You know better! But now the following code accesses the database 3 times and this is not correct:
PHP Code:
$segment  3
$perPage  10;
$page       = (int)$params[0];
if(!
$page)$page++;
$template 'default'
$model = new CustomerModel();
$validation = \Config\Services::validation();
$validation->setRules([
        
'name'      => ['label' => 'Name''rules' => 'required|max_length[50]'],
        
'email'  => ['label' => 'e-mail''rules' => 'required|max_length[100]'],
$model->where('active','yes');

$total $model->countAllResults(false);

$data   
 
   'table' =>  $model->paginate($perPage,$template,$page),
    
'pagerlinks' =>  $model->pager->Makelinks($page,$perPage,$total,$template.'_full',$segment)
    ]; 

The first call to the COUNT database: $total = $model->countAllResults(false);
The second two calls to the COUNT and * DB: $model->paginate($perPage,$template,$page)