Custom Query Pagination Cakephp

Question

I have a custom query in my controller and I would like to implement the custom query pagination I found on cakephp.org but their example is not similar to mine. Can someone please help me paginate this result in my view:

    $cars = $this->Car->query(" select Car.id, Car.make, Car.model, Car.year, Car.description, CarImage.thumbnail
               from cars Car
               inner join car_images CarImage on Car.default_image_id = CarImage.id
               where Car.make like '" . $category . "'
               order by Car.created DESC
               limit 10");
 $this->set('cars', $cars);

Answer 1

mysql limit clause support volume and offset, so 10 results per page....

select * from table limit 0, 10

page 1

select * from table limit 10, 10

page 2

select * from table limit 20, 10

page 3

select * from table limit ($page-1)*$perPage, $perPage

Generic

Answer 2

Implement paginate and paginateCount in your model:

function paginate($conditions, $fields, $order, $limit, $page, $recursive, $extra)
{
    return $this->query('SELECT ...');
}

function paginateCount($conditions, $recursive, $extra)
{
    return $this->query('SELECT COUNT(.....');
}

Also check out the paginate function in: cake/libs/controller/controller.php

Answer 3

This looks like it should be able to be done without resorting to using custom pagination.

You should set in the following relationships in your models:

Car hasMany (or hasOne) CarImage
CarImage belongsTo Car

Then you should be able to get this exact data using the following:

<?php
class CarsController extends AppController {
  var $paginate = array('limit'=>'10',
                        'order'=>'Car.created',
                        'fields'=>array('Car.model','Car.year','Car.description',
                                        'CarImage.thumbnail'));

  function test() {
    // not sure where you are getting the where clause, if its from some form
    // you'll want to check look in $this->data
    $category = "somevalue";
    // set the LIKE condition
    $conditions = array('Car.make LIKE' => '%'.$category.'%');
    // apply the custom conditions to the pagination
    $this->set('cars', $this->paginate($conditions);
  }
}
?>

For more information on complex find conditions (which can be applied to paginate by passing in an array like I did in the above example): http://book.cakephp.org/view/74/Complex-Find-Conditions

Answer 4

mysql limit clause support volume and offset, so 10 results per page....