CakePHP 3 - how to filter data (Collection) using pagination?

Need Help
1

The combination of Collection function (in my case ‘filter’) and paginate() causes a error. The simplified PHP code is below:

$this->loadModel(‘Rooms’);
$rooms = $this->Rooms->find(‘all’)->formatResults(function ($results) {
return $results->map(function ($row) {

  ... 
  $row->free_booking=0;      
  if($someCondition) $row->free_booking=1;
  ...
                           
  return $row;
 });        
 })->filter(function ($booking, $key) {
     return $booking->free_booking == 1;
 });
 
 $this->set('rooms',$this->paginate($rooms));

The error is below:

Error: Call to undefined method ArrayIterator::alias()
File …/public_html/vendor/cakephp/cakephp/src/Controller/Component/PaginatorComponent.php
Line: 174

How to solve this problem?

2

I do something like:

       $query = $this->Dogs->find()
            ->where([
                'dogname LIKE' => $psch . '%'
            ]);
        
       if ($aval == "n") {
            $query->Where(['adopted' => 1]);
        } else if ($aval == "y") {
            $query->Where(['adopted' => 0]);
        }
       $query->orderDesc('lastedit');
       $this->paginate = [
            'limit' => 5
        ];
       $this->set('title', 'Dogs');
       $this->viewBuilder()->setLayoutPath('/Dog');
       $this->viewBuilder()->setLayout('indexptp');
        $dogs = $this->paginate($query);
        $this->set([
            'dogs' => $dogs
        ]);
        $this->render('/Dogs/indexp');
3

Thank you for your reply. Unfortunately, the value of $ row-> free_booking variables can only be obtained within inside formatResults. $someCondition is extremely complex.
If query looks like this:

$this->loadModel(‘Rooms’);
$rooms = $this->Rooms->find(‘all’)->formatResults(function ($results) {
return $results->map(function ($row) {

  ... 
  $row->free_booking=0;      
  if($someCondition) $row->free_booking=1;
  ...
                           
  return $row;
 });        
 })->filter(function ($booking, $key) {
     return $booking->free_booking == 1;
 })->toArray;
 
 $this->set('rooms',$rooms);

Filtration works without any problems, but the application speed is very slow because it processes over 1000 records. The goal is to keep good application performance and this can not be achieved with $this->set('rooms',$rooms);

4

What are you doing, getting a large array and paginating that. That is not paginating.

You have to paginate by only getting say 10 or 20 results at a time. Pass query parameters to append the paginator:

<div class="paginator">
    <ul class="pagination">
            <?= $this->Paginator->first(__('<<')) ?>
            <?= $this->Paginator->prev(__('< ')) ?>
            <?= $this->Paginator->numbers([
                'modulus' => 2,
               'url' => [
                '?' => [
            'psch' => $psch,
            'aval' => $aval
        ]
    ]
]) ?>
            <?= $this->Paginator->next(__('>')) ?>
            <?= $this->Paginator->last(__('>>')) ?>
    </ul>
    <p><?= $this->Paginator->counter(['format' => __('Page {{page}} of {{pages}}, showing {{current}} record(s) out of {{count}} total')]) ?></p>
</div>

This part:

            'psch' => $psch,
            'aval' => $aval

Other wise write regular sql like:

    public function getPets($offset = "", $rowsperpage = "", $petsearch = "")
    {
        $pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
        $petsearch = $petsearch . "%";
        if (Auth::chkRole('admin') === true) {
            return DB::select("SELECT * FROM " . PREFIX . "pets " . $pagingQuery);
        }
        $authid = Auth::authId();
        return DB::select("SELECT * FROM " . PREFIX . "pets WHERE ownerid = :authid " . $pagingQuery, ["authid" => $authid]);
    }

And use a paginator like: