Does CSqlDataProvider influence CListView's pagination mode?

aslan · September 3, 2012, 4:44pm

I use CSqlDataProvider as the dataProvider of a CListView. When I click page 2, the entire webpage is refreshed, and I can see in the address bar like xxx/index.php/module/3?concept_id_page=2. This is obviously not an ajax mode.

For sure that I want the pagination is in the ajax mode, rather than refreshing the entire webpage.

Can someone please tell me what the problem is?

btw, I read class reference, to say, we can choose the pagination mode as normal (refresh entire webpage) or ajax, but I still cannot figure out how to choose the mode. And I am not sure whether I can specify the ajax mode to solve my problem.

Below is my code:

In controller:




$count=Yii::app()->db->createCommand($sql)->queryScalar();

  $dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    'id'=>'concept_id',

    'keyField'=>'concept_id',

    'sort'=>array(

      'attributes'=>array(

        'create_at',

      ),

    ),

    'pagination'=>array(

      'pageSize'=>5,

    ),

  ));

view:




<?php $this->widget('zii.widgets.CListView', array(

    'dataProvider'=>$this->getConcepts($model->module_id),

    'itemView'=>'_item',

    'summaryText'=>'',

)); ?>

_item.php




<p><span class="title"><?php echo $data['name']?></span></p>

This is the first time that I use CSqlDataProvider. That is because I want to write sql to join 3 tables (MANY TO MANY TO MANY). Besides the problem mentioned above, the other question is how I can access the attribute like $data->name rather than $data[‘name’].

softark · September 4, 2012, 10:24am

Hi aslan,

Just one thing that I noticed:

I think you should use a different sql for the count, as you see in the reference of CSqlDataProvider:




$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();

$sql='SELECT * FROM tbl_user';

$dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    ...

http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

aslan · September 4, 2012, 12:55pm

softark:

Hi aslan,

Just one thing that I noticed:

I think you should use a different sql for the count, as you see in the reference of CSqlDataProvider:
$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();

$sql='SELECT * FROM tbl_user';

$dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    ...
http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

Hi softark,

yeah i modified the code as your suggestion, but unfortunately it still not works. I past all related code blow, pleas have a look:

Controller:




class ModuleController extends GxController {

	

	public function actionView($id) {

		$this->layout='//layouts/column1_1';

		$model = $this->loadModel($id, 'Module');

		$dataProvider = $this->getConcepts($model->module_id);

		$this->render('view', array(

			'model' => $model,

			'dataProvider' => $dataProvider,

		));

	}


	public function getConcepts($module_id) {


		$sql='select'

		.' c.concept_id,'

		.' c.name,'

		.' c.description,'

		.' lc.lastaction_at,'

		.' lc.status'

		

		.' from'

		.' tpl_concept as c'

		.' join tpl_learner_concept as lc on c.concept_id = lc.concept_id'

		.' join tpl_module_concept as mc on lc.concept_id=mc.concept_id'

		

		.' where'

		.' mc.module_id='.$module_id

		.' and lc.learner_id='.Yii::app()->user->id;

			

		$sql.=' order by lc.lastaction_at desc';

		

		$sql2='select count(c.concept_id)'

		

		.' from'

		.' tpl_concept as c'

		.' join tpl_learner_concept as lc on c.concept_id = lc.concept_id'

		.' join tpl_module_concept as mc on lc.concept_id=mc.concept_id'

		

		.' where'

		.' mc.module_id='.$module_id

		.' and lc.learner_id='.Yii::app()->user->id;


		

		$count=Yii::app()->db->createCommand($sql2)->queryScalar();

		$dataProvider=new CSqlDataProvider($sql, array(

		    'totalItemCount'=>$count,

			'id'=>'concept_id',

			'keyField'=>'concept_id',

		    'sort'=>array(

		        'attributes'=>array(

		             'lastaction_at',

		        ),

		    ),

		    'pagination'=>array(

		        'pageSize'=>5,

		    ),

		));

		

		

		return $dataProvider;

	}

view.php




<?php $this->widget('zii.widgets.CListView', array(

	'dataProvider'=>$dataProvider,

	'itemView'=>'_item',

	'summaryText'=>'',

)); ?>

_item.php




<p><span class="title"><?php echo $data['name']?></span></p>

Please where are the problems?

softark · September 4, 2012, 1:47pm

I’m not sure, but there are 3 points that I noticed. 2 of them could be a cause of the trouble.

Is ‘concept_id’ really suitable for ‘keyField’?

I mean, are you sure that values of ‘concept_id’ column are unique in your result set?

If you have duplicated values for ‘concept_id’, then you have to make up a unique value concatenating some columns.




$sql = 'select'

    . ' concat(c.id, ",", lc.id, ",", mc.id) as concat_id'

    . ' c.concept_id,'

    ...


$dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    'keyField'=>'concat_id',

    ...

Where is ‘create_at’?

You should include ‘create_at’ in your select, or remove ‘create_at’ from ‘sort.attributes’.

You seems to have misunderstood ‘id’ property of the data provider.

However, I don’t think it’s the cause of the trouble. Setting it to ‘concept_id’ should not cause any trouble.

aslan · September 4, 2012, 2:16pm

softark:

I’m not sure, but there are 3 points that I noticed. 2 of them could be a cause of the trouble.

Is ‘concept_id’ really suitable for ‘keyField’?

I mean, are you sure that values of ‘concept_id’ column are unique in your result set?

If you have duplicated values for ‘concept_id’, then you have to make up a unique value concatenating some columns.
$sql = 'select'

    . ' concat(c.id, ",", lc.id, ",", mc.id) as concat_id'

    . ' c.concept_id,'

    ...


$dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    'keyField'=>'concat_id',

    ...

I believe ‘concept_id’ is suitable, because

I join these 3 tables:

table 1. tpl_module_concept: MANY_TO_MANY, but in a specific webpage, I mean, if I set module_id=3, then this should be ONE_TO_MANY (one ‘module’ has many ‘concepts’).

table 2. tpl_learner_concept: MANY_TO_MANY, the same reason as table 1, for specific user, he is learning many ‘concepts’.

table 3. tpl_concept: information of ‘concept’ such as name, description, etc.

Am I right? is ‘concept_id’ suitable for ‘keyField’?

yeah I remove the ‘create_at’, I wont use it in fact. The one I use is ‘lastaction_at’.

As you can see, I set both ‘id’ and ‘keyField’ as the same value ‘concept_id’, I have to confess that I misunderstood these two properties. Actually, the ‘id’ should be the unique identifier for the data provider, rather than for the row of database table. Am I right? So I should Appropriately set ‘id’ as i.e. c_list, but not the name of one of the columns in the database table. right?




'id'=>'concept_id',

'keyField'=>'concept_id',

softark · September 4, 2012, 2:38pm

I see.

So now I don’t see anything suspicious in your code for CListView and CSqlDataProvider.

If your list view and its pager are working as expected (except for ajax), then I think there’s no problem in CListView and CSqlDataProvider.

One thing comes to my mind (from another one of your posts) is a mismatching tags in your view HTML … an opening tag without a closing tag, or vice versa.

Would you please check your view script for that?

It’s better to use ‘view page source’ function of your browser. DOM tree in the developer’s tool might not show the raw source code as is, because the html parser of the browser will recover and hide the errors.

aslan · September 4, 2012, 3:00pm

Yes, everything works well, EXCEPT the ajax, which is actually the problem that I want to solve.

I checked it and I think nothing is wrong with it. I’ve update the format in that post, you can have a look at it again if you like.

In fact, these two posts are asking the same question - ajax pagination dose not work for CListView. I thought the problem was something related to the CSqlDataProvider, so I made this post. But for now, with your help, I think CSqlDataProvider, CArrayDataProvider, CActiveDataProvider, etc. all of them can be used for the dataprovider of the CListView (and CGridView), and the only difference is how data set is made. Am I right?

Thanks for your help!

But, still, my problem (in the other post) - ajax pagination dose not work for CListView - hasn’t been solved

softark · September 4, 2012, 3:43pm

Please make it sure by checking it from <body> to </body>.

Yes, you are right.

The javascript error should be a hint, but I wonder where to begin.

Updating the framework to the latest stable version (1.1.12) might help, if you haven’t done it already.

aslan · September 4, 2012, 4:32pm

I checked every line of the code. Nothing wrong was found.

But you are right, there should be something wrong with the javascript, which could be caused by other html codes

I delete all codes in the page, except three $this->widget(‘zii.widgets.CListView’, array(…

Then, they all work in ajax mode!

Now I know what I should do~

Thank you very much, softark!

aslan · September 4, 2012, 4:58pm

problem solved

softark · September 4, 2012, 11:15pm

Yeah, great!