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?

Continual

Please check the answer provided whether it is useful. Thanks. – Berliner

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

Apophasis

this is not working with joins, I didn't check that with normal query. – Brander

Answer 2

If you are using any Association Relation Ship Model it's very simple http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

Pronation

No. I need to know whether is it possible to paginate the resultset of above query without the help of any models – Continual