CakePHP Pagination count not matching query?

Question

I've got a fairly modified pagination query using a number of Joins etc - but for some reason the paginator->counter() never matches the results from the count query.

You can see it in action at http://dev.qreer.com/ - by choosing various options on the LHS navigation, the query output is below and the paginator count appears to be pretty random.

Any idea where I can start looking to debug this?

In the Jobs Controller:

    $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 (there's inner joins for all the join tables and data tables):

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

Sample Condition:

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

Answer 1

It's because of the group by I found a workaround. I'd love to put the link but i've lost it, so i'll post 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 in the app_model and it works fine 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

Figured it out:

The paginator counter relies on $this->find('count') to return an integer of the total of the results, which for some reason, doesn't like the 'group' parameter. So following the Custom Query Pagination (which also recommends at the bottom of the page to do the count yourself 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 it all seems to be working perfectly.

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

Answer 3

function paginateCount($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 in your model and change the field name and model name. Using this function you can customize you own query count.