Hi,
I need to use a fairly complicated SQL query for a CGridView. I see I’m not the only person who has trouble with CSQLDataProvider and Pagination.
I have read through all the forum posts I could find and I think I have implemented the suggestions provided in those posts but I’m still having trouble.
I’ve:
-
made sure I have specified ‘totalItemCount’ and its calculating correctly
-
made sure there is no ‘;’ at the end of my sql statement
For the GridView - the first page loads fine with the correct total number of records and pagination links at the bottom.
But when I click one of the pagination links or ‘next’, I can see the pager animation running but no data is output to the screen.
I’ve added some Yii::trace statements in CSQLDataProvider.php fetchData() function, and check in the application log. I can see that my trace statements are written on first page load, and the LIMIT clause is added to the sql correctly – but there are no trace statements on pagination requests.
This suggests that SQLDataProvider->fetchData() is not being called for pagination requests.
Here is my snippet from my controller:
$sql = "SELECT i.ID,i.travellerID,i.first_name,i.last_name,i.email,i.campus,i.studentNum, g.goGlobalID, g.term, g.session FROM (".$inner_sql.") as i LEFT JOIN experience_go_global as g ON i.ID=g.experienceID ".$outer_where;
$count_sql = "SELECT count(*) FROM (".$sql.") as j ";
$count = Yii::app()->db->createCommand($count_sql)->queryScalar();
$dataProvider = new CSqlDataProvider($sql, array(
'totalItemCount'=>$count,
'sort'=>array(
'attributes'=>array(
'ID',
),
),
'pagination'=>array('pageSize'=>5),
));
And in my view:
if(!empty($dataProvider)){
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'experience_sql_grid',
'dataProvider'=>$dataProvider,
'columns'=>array(
array('name'=>'First Name','value'=>'$data["first_name"]'),
array('name'=>'Last Name','value'=>'$data["last_name"]'),
array('name'=>'Stud. No.','value'=>'$data["studentNum"]'),
array('name'=>'email',
'type'=>'raw', //because of using html-code
'value'=>'CHtml::mailto("email",$data["email"])'),
array('name'=>'Campus','value'=>'($data["campus"]=="Vancouver")? "V":"O"'),
array('name'=>'session','value'=>array($experience,'gridSession')),
array('name'=>'start','value'=>array($experience,'gridEarliestDestinationStart')),
array('name'=>'end','value'=>array($experience,'gridLatestDestinationEnd')),
array('name'=>'highest level','value'=>array($experience,'gridHighestDestinationLevel')),
array('name'=>'destinations',
'type'=>'raw', //because of using html-code
'value'=>array($experience,'gridDestinationLinks')),
array('name'=>'Main Reg Complete','value'=>array($experience,'gridTravellerComplete')),
array('name'=>'Dest(s) complete','value'=>array($experience,'gridDestinationComplete')),
array('name'=>'','type'=>'raw','value'=>array($experience,'gridButtons')),
),
));
}else{
print "<p>No Data!</p>";
}
I have also checked for javascript errors in my console and there are none.
Hoping someone has run into this before and has some ideas. Hopefully I’m just missing some obvious step somewhere.
Background info using:
-
LAMP environment
-
Yii-1.1.12 - and it’s loading[list]
-
jQuery JavaScript Library v1.7.2
-
jQuery UI - v1.8.22
[/list]