CODEWITHSUNDEEP
phpcakephppagination
Alexandre Strapacao G. Vianna
Alexandre Strapacao G. Vianna

Reputation: 399

CakePHP custom query paginator: custom paginate function is not called

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 ?

Upvotes: 0

Views: 1632

Answers (1)

Alexandre Strapacao G. Vianna
Alexandre Strapacao G. Vianna

Reputation: 399

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']);

Upvotes: 1

Related Questions