CakePHP: How to make the paginator component use distinct counting?

I am making simple pagination which using this code:

$paginate = array(
    'limit' => 30,
    'fields' => array('DISTINCT Doctor.id','Doctor.*'),
    'order' => array('Doctor.id' => 'desc'),
    'joins' => array(
        array('table' => 'doctors_medical_degrees',
            'alias' => 'DoctorsMedicalDegree',
            'type' => 'INNER',
            'conditions' => array(
                'Doctor.id = DoctorsMedicalDegree.doctor_id',
            )
        ),
    ),
    'recursive' => -1,
);
$this->Paginator->settings = $paginate;
$data = $this->Paginator->paginate('Doctor');

Now the problem is I am using Inner join so for Distinct result I am using Distinct Doctor.id, but the cakephp when doing query for pagination the count query not including Distinct Doctor.id

'query' => 'SELECT COUNT(*) AS `count` FROM `pharma`.`doctors` AS `Doctor` INNER JOIN `pharma`.`doctors_medical_degrees` AS `DoctorsMedicalDegree` ON (`Doctor`.`id` = `DoctorsMedicalDegree`.`doctor_id`)'

as you can see No

COUNT(DISTINCT Doctor.id)

so pagination return more number of result which it can actually return for

Solution

The problem is that the paginator doesn't pass the fields to the find('count') call, so by default it will always count on *.

But even if it would pass the fields, passing an array would make the find('count') call expect that the field to count is passed as a COUNT() expression, ie something like

'fields' => array('COUNT(DISTINCT Doctor.id) as `count`')

However that won't work with the paginator anyways, so what you need is a customized find('count') call.

Custom query pagination to the rescue

See Cookbook > Pagination > Custom Query Pagination for more information.

Custom query pagination is probably your best bet, that way it's totally up to you how counting is being done.

For example you could make use of the extra values passed by the paginator component, that way you could pass the field to count on to the find('count')` call, something like this (untested example code):

class Doctor extends AppModel {
    // ...

    public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
        $parameters = compact('conditions');
        if($recursive != $this->recursive) {
            $parameters['recursive'] = $recursive;
        }

        if(!empty($extra['countField'])) {
            $parameters['fields'] = $extra['countField'];
            unset($extra['countField']);
        }

        return $this->find('count', array_merge($parameters, $extra));
    }
}

$this->Paginator->settings = array(
    'limit' => 30,
    'fields' => array('DISTINCT Doctor.id','Doctor.*'),
    // ...
    'countField' => 'DISTINCT Doctor.id'
);
$data = $this->Paginator->paginate('Doctor');

This should then create a COUNT query that looks like

SELECT COUNT(DISTINCT Doctor.id) AS `count` ...