How to paginate custom query result in cakephp

Question

I'm working on a cakephp project and new to cakephp. As mentioned in the title I need to paginate the result set of a mysql query in order to display them in a view.

In this way I can paginate results from the "Asin" model.

$this->paginate =array('Asin'=>array(
            'fields' => array('sku','fnsku'),
            'conditions' => $marketplace,
            'page' => 1, 'limit' => 20,
            'order' => array('Asin.asin' => 'asc')
        )
$data = $this->paginate('Asin',$criteria);

And I need a way to paginate the result set of the below query as well.

$resultset = $this->Asin->query("SELECT * FROM products INNER JOIN asins ON products.id=asins.id ORDER BY products.id");

how can I add pagination?

Answer 1

CakePHP uses two method to manage pagination queries, that are paginate and paginateCount, they are used to get page data and total record count respectively. To have pagination work with your custom queries, we will need to implement both above functions in our model file where you want to have pagination to work with custom queries.

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

    // Mandatory to have
    $this->useTable = false;
    $sql = '';

    $sql .= "Your custom query here just do not include limit portion";

    // 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 .= "Your custom query here just do not include limit portion";

    $this->recursive = $recursive;

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

    return count($results);
}

Then finally in controller action

// Do not forgot to set this, not sure why
$this->Asin->recursive = 0;

// Setting up paging parameters
$this->paginate = array('Asin'=>array('limit'=>5));

// Getting paginated result based on page #
$this->set('userData', $this->paginate('Asin'));