CakePHP-使用DISTINCT时，分页总数与实际计数不同 [英] CakePHP - Pagination total count differs from actual count when using DISTINCT

问题描述

我有一个find方法，该方法使用DISTINCT子句从模型中获取结果。控制器代码如下所示：

I have a find method that uses a DISTINCT clause to get results from my Model. The Controller code looks like below

$options = array(
    'limit' => 10,
    'fields' => array(
        'DISTINCT id', 'title', 
    ),
    'contain' => array(
        'Dealer' => array('id'),
    ),
    'paramType' => 'querystring'
);

$this->Paginator->settings = $options;
$cars = $this->Paginator->paginate('Car'); // returns 6 distinct rows

上述查询返回6个不同的行和12个总行。因此，当我显示时，屏幕上会显示6个不同的行

The above query return 6 distinct rows and 12 total rows. So when I am displaying, the screen shows 6 distinct rows

但是在视图中，当我使用

However in the View, when I use

echo $this->Paginator->param('count'); // returns 12

我得到的计数为12

我检查了SQL日志，发现count查询未使用distinct子句。知道如何覆盖Paginator计数查询以使用 DISTINCT 子句吗？

I checked the SQL Log and noticed that the count query is not using the distinct clause. Any idea how I can override the Paginator count query to use the DISTINCT clause?

推荐答案

找到了解决方案，

1. 在控制器中，将distinct作为具有其他分页选项的数组参数添加。因此，如果我尝试一次检索一次清单中有10辆汽车的汽车列表，则这些选项将在fields参数中包含DISTINCT子句，并且还将添加一个名为distant的单独参数，如下所示

1. In controller add distinct as an array parameter with other pagination options. So if I was trying to retrieve a list of Cars in my inventory with 10 cars at a time, the options would have a DISTINCT clause in the fields parameter and a separate parameter called distinct would also be added as shown below

$ options = array（
'conditions => $ conditions，
'joins'=> $ joins，
'limit'=> 10，
'fields'=> array（
'DISTINCT Car.id'，'title'，'user_id'），
'contain'=> array（
'Dealer'= > array（'id'），
），
'paramType'=>'querystring'，
'distinct'=>'Car.id'
）;

$options = array( 'conditions' => $conditions, 'joins' => $joins, 'limit' => 10, 'fields' => array( 'DISTINCT Car.id', 'title', 'user_id'), 'contain' => array( 'Dealer' => array('id'), ), 'paramType' => 'querystring', 'distinct' => 'Car.id' );

$ this-> Paginator-> settings = $ options;
$ cars = $ this-> Paginator-> paginate（'Car'）;

$this->Paginator->settings = $options; $cars = $this->Paginator->paginate('Car');

在模型中，使用以下功能覆盖原始内容 paginateCount 方法

In Model, use the below function to override the original paginateCount method

公共函数paginateCount（$ conditions = null，$ recursive = 0，$ extra = array（） ）{
$ parameters = compact（'conditions'，'recursive'）;
if（isset（$ extra [’distinct’]））{
$ parameters [’fields’] =‘DISTINCT’。 $ extra ['distinct'];
$ count = $ this-> find（'count'，array_merge（$ parameters，$ extra））;
} else {
//常规分页
$ count = $ this-> find（'count'，array_merge（$ parameters，$ extra））;
}
返回$ count；
}

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) { $parameters = compact('conditions', 'recursive'); if (isset($extra['distinct'])) { $parameters['fields'] = 'DISTINCT ' . $extra['distinct']; $count = $this->find('count', array_merge($parameters, $extra)); } else { // regular pagination $count = $this->find('count', array_merge($parameters, $extra)); } return $count; }

视图无变化

这篇关于CakePHP-使用DISTINCT时，分页总数与实际计数不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！