Welcome to The Cookbook

Fix me: Please add an example where overriding paginate is justified

Should you need to create custom queries to generate the data you want to paginate, you can override the paginate() and paginateCount() model methods used by the pagination controller logic.

Before continuing check you can't achieve your goal with the core model methods.

The paginate() method uses the same parameters as Model::find(). To use your own method/logic override it in the model you wish to get the data from.

/**
 * Overridden paginate method - group by week, away_team_id and home_team_id
 */
function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
	$recursive = -1;
	$group = $fields = array('week', 'away_team_id', 'home_team_id');
	 return $this->find('all', compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive', 'group'));
}

1. /**
2. * Overridden paginate method - group by week, away_team_id and home_team_id
3. */
4. function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
5. $recursive = -1;
6. $group = $fields = array('week', 'away_team_id', 'home_team_id');
7. return $this->find('all', compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive', 'group'));
8. }

You also need to override the core paginateCount(), this method expects the same arguments as Model::find('count'). The example below uses some Postgres-specifc features, so please adjust accordingly depending on what database you are using.

/**
 * Overridden 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. * Overridden 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. }

The observant reader will have noticed that the paginate method we've defined wasn't actually necessary - All you have to do is add the keyword in controller's $paginate class variable.

/**
* Add GROUP BY clause
*/
var $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. var $paginate = array(
5. 'MyModel' => array('limit' => 20,
6. 'order' => array('week' => 'desc'),
7. 'group' => array('week', 'home_team_id', 'away_team_id'))
8. );

However, it will still be necessary to override the paginateCount() method to get an accurate value.