CakePHP custom query paginator: custom paginate function is not called

Question

I build a custom query and tried use the default paginator, like this:

WodsController.php

$userId = $this->Auth->user('id');
$connection = ConnectionManager::get('default');
$result = $connection->execute("SELECT  wods.id, wods.titulo , wods.dia , wods.tempo, wods.repeticoes ,userwods.user_id FROM wods
LEFT JOIN userwods ON userwods.wod_id = wods.id WHERE userwods.user_id is null or userwods.user_id=4 order by wods.dia desc limit 50")->fetchAll('assoc');
$results = array();
foreach ($result as $r) {
    $entity = $this->Wods->newEntity($r);
    array_push($results, $entity);
}
$wods = $this->paginate($results);
$this->set('_serialize', ['wods']);

I got this error "Unable to locate an object compatible with paginate".

Now I'm tryng implement custom query paginator, but it's not working. I implemented paginate and paginateCount functions in the model. Wods.php file:

public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
    $recursive = -1;

    $this->useTable = false;
    $sql = '';

    $sql .= "SELECT  wods.id, wods.titulo , wods.dia , wods.tempo, wods.repeticoes ,userwods.user_id FROM wods LEFT JOIN userwods ON userwods.wod_id = wods.id WHERE userwods.user_id is null or userwods.user_id=4 order by wods.dia desc limit ";

    // Adding LIMIT Clause
    $sql .= (($page - 1) * $limit) . ', ' . $limit;

    $results = $this->query($sql);

    return $results;
}
public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {

    $sql = '';

    $sql .= "SELECT  wods.id, wods.titulo , wods.dia , wods.tempo, wods.repeticoes ,userwods.user_id FROM wods LEFT JOIN userwods ON userwods.wod_id = wods.id WHERE userwods.user_id is null or userwods.user_id=4 order by wods.dia desc";

    $this->recursive = $recursive;

    $results = $this->query($sql);

    return count($results);
}

In the controller WodsController.php

public function index()
{
    $this->Wods->recursive = 0;

    $this->paginate = array('Wods'=>array('limit'=>10));

    $this->set('wods', $this->paginate('Wods'));
}

But the custom paginator is not called, it continues calling the default paginate function. Why ?

Are you just trying to change the number rows per page to 10? If so, you can use the default paginate function and just use $this->paginate = array('limit' => 10); And call it with $this->paginate(); — dragmosh
My problem with the default paginator is make it work when I have a custom query. I tried and I recived this: "Unable to locate an object compatible with paginate" — Alexandre Strapacao G. Vianna
It looks like you're trying to use custom pagination to circumvent the CakePHP ORM. I'd advise against that. You can write most custom queries (including joins, groups, subqueries, and order by parameters) using CakePHP's ORM and set the paginator to run that. — dragmosh

Alexandre Strapacao G. Vianna Alexandre Strapacao G. Vianna · Accepted Answer · 2016-03-22T05:06:15

Following dragmosh advise (thanks), I investigate CakePHP ORM custom queries builder. In this solution I used find() function with specific options, after I called the default paginator:

$query = $this->Wods->find()
                ->select(['Wods.id', 'Wods.titulo','Wods.dia','Wods.rounds','Wods.tempo','Wods.repeticoes','Userwods.user_id'])
                ->join([
                        'table' => 'Userwods',
                        'alias' => 'Userwods',
                        'type' => 'LEFT',
                        'conditions' => 'Userwods.wod_id = Wods.id',
                ])
                ->where(function ($exp, $q) {
                    return $exp->isNull('Userwods.user_id');})
                ->orWhere(['Userwods.user_id' => 4])
                ->contain(['Userwods'])
                ->autoFields(true);
        $wods = $this->paginate($query);

        $this->set(compact('wods'));
        $this->set('_serialize', ['wods']);

CakePHP custom query paginator: custom paginate function is not called

1 Answers