Bienvenido a El Cookbook

Should you need to create custom queries to generate the data you want to paginate, you can override the paginate() and paginateCount() methods used by the pagination helper. The paginate() method uses the same parameters as Model::find(). To use your own paginate() method, create a paginate() method in the model you wish to get the data from.

/**
 * Custom paginate method
 */
function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
	$conditions[] ="1 = 1 GROUP BY week, away_team_id, home_team_id";
	$recursive = -1;
	$fields = array('week', 'away_team_id', 'home_team_id');
	 return $this->findAll($conditions, $fields, $order, $limit, $page, $recursive);
}

1. /**
2. * Custom paginate method
3. */
4. function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
5. $conditions[] ="1 = 1 GROUP BY week, away_team_id, home_team_id";
6. $recursive = -1;
7. $fields = array('week', 'away_team_id', 'home_team_id');
8. return $this->findAll($conditions, $fields, $order, $limit, $page, $recursive);
9. }

You also need to override the default paginateCount() method by creating one in your model. This method should accept the same arguments as Model::findCount(). The example below uses some Postgres-specifc features, so please adjust accordingly depending on what database you are using.

/**
 * Custom paginateCount method
 */
function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
	$sql = "SELECT DISTINCT ON(week, home_team_id, away_team_id) week, home_team_id, away_team_id FROM games";
	$this->recursive = $recursive;
	$results = $this->query($sql);
	return count($results);
}

1. /**
2. * Custom paginateCount method
3. */
4. function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
5. $sql = "SELECT DISTINCT ON(week, home_team_id, away_team_id) week, home_team_id, away_team_id FROM games";
6. $this->recursive = $recursive;
7. $results = $this->query($sql);
8. return count($results);
9. }

More recently (RC2), with the addition of group keyword in CakePHP's Model::find() method one can avoid overriding paginate(). All you have to do is add the keyword in controller's $paginate class variable.

/**
* Add GROUP BY clause
*/
public $paginate = array(
	'MyModel' => array('limit' => 20, 
                           'order' => array('week' => 'desc'),
                           'group' => array('week', 'home_team_id', 'away_team_id'))
                          );

1. /**
2. * Add GROUP BY clause
3. */
4. public $paginate = array(
5. 'MyModel' => array('limit' => 20,
6. 'order' => array('week' => 'desc'),
7. 'group' => array('week', 'home_team_id', 'away_team_id'))
8. );

paginateCount() still has to be overridden as described above though.