CakePHP Does the pagination number not match the query?

Question

I have a pretty modified pagination request using multiple links, etc., but for some reason the paginator-> counter () parameter never matches the count query results.

You can see it in action at http://dev.qreer.com/ - by choosing various LHS navigation options, the query output is lower, and the paginator count seems rather random.

Any idea where I can start debugging this?

In the controller of work orders:

$this->paginate = $this->Job->paginateParams($data); $jobs = $this->paginate('Job'); $this->set(compact('jobs')); 

In the model:

 function paginateParams($data = null){ //lots of joins + conditions return array('contain' => $contain, 'recursive' => 0,'joins' => $joins, 'conditions' => $conditions, 'group' => 'Job.id', 'order' => $order); } 

Sample Join (internal joins for all join tables and data tables):

  array( 'table' => 'education_backgrounds', 'alias' => 'EducationBackground', 'type' => 'INNER', 'conditions' => array('EducationBackgroundsJobs.education_background_id = EducationBackground.id'), ), 

Example condition:

  'EducationBackground.name' => array('Aerospace Engineering'); 

Answer 1

Due to group by I found a workaround. I would like to put a link, but I lost it, so I will send the code:

 public function paginateCount($conditions = null, $recursive = 0, $extra = array()) { $parameters = compact('conditions', 'recursive'); if (isset($extra['group'])) { $parameters['fields'] = $extra['group']; if (is_string($parameters['fields'])) { // pagination with single GROUP BY field if (substr($parameters['fields'], 0, 9) != 'DISTINCT ') { $parameters['fields'] = 'DISTINCT ' . $parameters['fields']; } unset($extra['group']); $count = $this->find('count', array_merge($parameters, $extra)); } else { // resort to inefficient method for multiple GROUP BY fields $count = $this->find('count', array_merge($parameters, $extra)); $count = $this->getAffectedRows(); } } else { // regular pagination $count = $this->find('count', array_merge($parameters, $extra)); } return $count; } 

I added it to app_model and it works great for me :)

Hope this helps

Edited: I found the link =)

http://wiltonsoftware.com/posts/view/custom-group-by-pagination-and-a-calculated-field

Answer 2

It turned out:

The paginator counter relies on $this->find('count') to return an integer number of totals, which for some reason does not like the "group" parameter. So, following the Custom Query Pagination (which also recommends doing the tally on the bottom of the page for any custom / modified pagination) - I added the following to my model:

 function paginateCount(){ $params = Configure::read('paginate.params'); $params['fields'] = 'DISTINCT (Job.id)'; unset($params['group']); unset($params['contain']); unset($params['order']); return $this->find('count', $params); } 

This overwrites the value with the correct one and everything seems to work fine.

Keeping in mind, I added Configure::write('paginate', array('params' => $this->paginate['Job'])); to my controller to access the pagination options.

Answer 3

PaginateCount function ($ conditions = null, $ recursive = 0, $ extra) {

  $db = $this->getDataSource(); $sql = $db->buildStatement( array( 'fields' => array('DISTINCT Gdi.id'), 'table' => $db->fullTableName($this), 'alias' => 'Gdi', 'limit' => null, 'offset' => null, 'joins' => isset($extra['joins'])?$extra['joins']:null, 'conditions' => $conditions, 'order' => null, 'group' =>isset($extra['group'])?$extra['group']:null ), $this ); $this->recursive = $recursive; $results = $this->query($sql); return count($results); } 

Just add this function to your model and change the field name and model name. With this function, you can configure your own request counter.