CakePHP：如何使分页器组件使用不同的计数？ [英] 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');

现在的问题是我使用内连接，因此我使用Distinct Doctor.id，但是cakephp在做分页查询时，查询不包括Distinct Doctor.id

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`)'

，您可以看到没有

COUNT（DISTINCT Doctor.id）

COUNT(DISTINCT Doctor.id)

所以分页返回更多的结果实际上返回

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

推荐答案

问题是paginator不传递字段到 find 'count'）调用，因此默认情况下它总是依赖于 * 。

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

但是即使它会传递字段，传递数组也会使 find（'count'）调用期望计数字段作为 COUNT（）表达式，例如

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`')

使用paginator无论如何，所以你需要的是一个自定义的 find（'count'）调用。

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

请参阅 Cookbook> Pagination>自定义查询分页 了解详情。

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.

例如，你可以利用分页器传递的额外的值组件，这样你可以传递字段来计数find（'count'）`调用，类似这样（未测试的示例代码）：

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');

然后应该创建一个 COUNT 看起来像

This should then create a COUNT query that looks like

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

这篇关于CakePHP：如何使分页器组件使用不同的计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！