CGridView + CSqlDataProvider > pagination issues

Yii 1.1 General Discussion
1

Hello,

I am experiencing some pagination problems with CGridView when used with CSqlDataProvider (I can see the pagination gadget at the bottom of the grid but all rows show on page 1).

Here is how I create the data provider in the controller:

$connection=Yii::app()->db;

$sql="SELECT c.id, c.firstname, c.opcional, a.line1, a.line2, a.city, a.city, a.zip, a.state, a.type FROM CustomerName c LEFT JOIN Address a ON c.id = a.customerid;";

$rowcountsql = "SELECT COUNT(*) FROM CustomerName c LEFT JOIN Address a ON c.id = a.customerid;";

$command = $connection->createCommand($rowcountsql);

$count=$command ->queryScalar(); // provide count for pagination

// create data provider that works with CGridView

$dataProvider=new CSqlDataProvider($sql, array(

'totalItemCount'=>$count,


'pagination'=>array(


    'pageSize'=>10,


),

));

$this->render(‘dynamic’,array(‘dataProvider’=>$dataProvider

));

And here is the code in the view:

<?php $this->widget(‘zii.widgets.grid.CGridView’, array(

'id'=&gt;'customer-address-grid',


'dataProvider'=&gt;&#036;dataProvider, 


'enablePagination'=&gt;true,


'ajaxUpdate'=&gt;true,


'enableSorting'=&gt;true,


'columns'=&gt;array(


	'firstname:html:First Name',


	'lastname:html:Last Name',


	'opcional:html:Optional',


	'line1:html:Line 1',


	'line2:html:Line 2',


	'city:html:City',


	'state:html:State',


	'zip:html:Zip',


	'type:html:Type'


),

)); ?>

The view displays the grid just fine, I can see the pagination gadget at the buttom with

Go to page: < Previous 1 2 Next > which is correct (there are 15 rows in the sample database, and

I set pageSize to 10). The PROBLEM IS that I can see ALL ROWS on page 1, and nothing on page 2!!!

I am a noob, I just got started with yii. Could you tell me if I missed something or

there is a pagination issue when using CGridView with CSqlDataProvider? If I pass the model created by gii

to the view I do not have any problems. I suspect yiiGridView.js is missing a parameter, but debugging with

"inspector" proved not to be as fruitful as I thought it would be.

Any thoughts?

Thank you :)

2

first just check you sql return correct result ( i.e 15 rows ).

just echo your query and run in any db manager.

the CGridView had to take pagination as default, other than you can set pagination in search() method of your model as,

return new CActiveDataProvider($this, array(

		'pagination'=&gt;array(


                            'pageSize'=&gt; '[b]no of record per page here[/b]',


         ),
3

sql returns 15 rows, no problem there. If the CGridView sets the pagination in the search() method of my model I am out of luck, because my model is a CSqlDataProvider, which does not have a search method.

4

Hi,

Maybe the semi-colon at the end of the sql is causing the trouble.

CSqlDataProvider will append "LIMIT", "OFFSET" and/or "ORDER" clauses according to the pagination and sort parameters. And my guess is that the appended parts are separated with the semi-colon and are ignored in the query.

Try instead:




$sql="SELECT ... ON c.id = a.customerid";
5

Hi softark,

Brilliant! You are right, mysql does ignore everything after the semi-colon and CSqlDataProvider pagination relies on the query.

I changed the statement to $sql="SELECT c.id, c.firstname, c.opcional, a.line1, a.line2, a.city, a.city, a.zip, a.state, a.type FROM CustomerName c LEFT JOIN Address a ON c.id = a.customerid"; (no semi-colon at the end and it works!

THANK YOU!!!!!!!!