Yii分页结果使用CSqlDataProvider [英] Yii Pagination Result using CSqlDataProvider

问题描述

我正在使用CSqlDataProvider进行分页。结果会显示在分页的前20页或首页。当我在网页上看到日志消息时，它使用的查询实际上是为所有页面选择前20行，这就是问题所在。我不知道如何改正它。

我的控制器有代码：

 <$ c 
 
 
 {
 $ sql =选择订单号，订单日期
从[订单] 
 WHERE CAST（订单日期作为日期）BETWEEN '01 -01- 2014'AND '31 -01-2014'; 
 
 $ count = Yii :: app（） - > db-> createCommand（'SELECT COUNT（*）FROM（'。$ sql。'）as count_alias'） - > queryScalar ）; 
 $ dataProvider = new CSqlDataProvider（$ sql，array（'keyField'=>'Order_Date'，
'totalItemCount'=> $ count，
'pagination'=> array（ 
'pageSize'=> 20，），））; 
 
 $ this-> render（'link'，array（'dataProvider'=> $ dataProvider））; 
}} 
  

现在，它用于count的查询是正确的（Log Message） ：

查询SQL：

  SELECT COUNT（*）FROM SELECT ordernumber，Order_Date 
 FROM [Orders] 
 WHERE CAST（Order_Date As Date）BETWEEN '01 -01-2014'AND '31 -01-2014'）as count_alias 
  
 
它用于页面2的查询是（日志消息）：
 
 <$ p $ （选择TOP 40 *订单号，订单日期
 FROM [订单] 
 WHERE CAST（订单日期为日期）
 BETWEEN '01 -01-2014'AND '31 -01-2014'）as [__inner__]）as [__outer__] 
  

<上面的查询实际上会给出前20行的结果。它与每个页面都有相同的问题。它将每个结果作为前20行。
所以第3页变成（日志消息）：

pre $ Ordernumber，Order_Date
FROM [Orders]
WHERE CAST（Order_Date As Date）
BETWEEN '01 -01-2014'AND '31 -01-2014'）
as [__inner__ ]）as [__outer__]

我能看到的唯一问题是它用来检索第2,3页的数据..等等。对于每一页，它都会返回相同的数据，因为每次选择TOP 20行时都是如此。我不明白这种行为的原因。当在分页过程中使用 SQL SERVER 作为数据库和 CSqlDataProvider 作为数据提供程序时，查询的实际外观如何？

解决方案

我找到了解决方案。
我必须将CMssqlCommandBuilder中的rewriteLimitOffsetSql函数更改为：

  return $ sql。OFFSET。$ offset。 ROWS FETCH NEXT。$ limit。ROWS ONLY; 
  

在函数中只有这一行，并删除其余的代码。很好，SQL SERVER开始支持OFFSET和FETCH。

I am using pagination with CSqlDataProvider. Result is shown properly for the first 20 or first page of the pagination. When I see the Log message on the web page the query it uses is actually selecting top 20 rows for all the pages and that's the problem. I have no idea how to correct it.

My controller has code:

public function actionLink()
 {
      $sql = "SELECT Ordernumber, Order_Date
                FROM [Orders]
                WHERE CAST(Order_Date As Date) BETWEEN '01-01-2014' AND '31-01-2014'";

      $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM (' . $sql . ') as count_alias')->queryScalar();
      $dataProvider = new CSqlDataProvider($sql, array('keyField' => 'Order_Date',
           'totalItemCount' => $count,
           'pagination' => array(
           'pageSize' => 20,),));

      $this->render('link',  array('dataProvider' => $dataProvider));
    }}      

And now the query which it uses for count is correct(Log Message):

Querying SQL:

SELECT COUNT(*) FROM (SELECT Ordernumber ,Order_Date
FROM [Orders]
WHERE CAST(Order_Date As Date) BETWEEN '01-01-2014' AND '31-01-2014') as count_alias

The query it uses for page 2 is(Log Message):

 SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 40 Ordernumber,Order_Date
 FROM [Orders]
 WHERE CAST(Order_Date As Date) 
 BETWEEN '01-01-2014' AND '31-01-2014') as [__inner__]) as [__outer__]

The above query actually will give result as first 20 rows. Its the same problem with every page. It gives every result as the same first 20 rows. So Page 3 becomes(Log Message):

 SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 60 Ordernumber,Order_Date
 FROM [Orders]
 WHERE CAST(Order_Date As Date) 
 BETWEEN '01-01-2014' AND '31-01-2014') 
 as [__inner__] ) as [__outer__]

The only problem I can see is the query it is using to retrieve the data for page 2,3.. and so on. For every page it returns the same data because every time it is selecting TOP 20 rows. I don't understand reason for this behavior. How does the query actually looks like when you use SQL SERVER as DB and CSqlDataProvider as data provider during pagination?

解决方案

I found the solution. I had to change the rewriteLimitOffsetSql function in CMssqlCommandBuilder to:

return $sql." OFFSET ".$offset." ROWS FETCH NEXT ".$limit." ROWS ONLY";

Just this one line in function and remove the rest of the code. Good that SQL SERVER started support for OFFSET and FETCH.

这篇关于Yii分页结果使用CSqlDataProvider的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！