CPagination and Blog Demo

Archives Yii 1.0.x Discussions
1

I was looking at the blog demo for how CPagination is implemented.  It seems that all records are returned and only a subset at a time is dislayed.  I imagine this would be a resource hog if the recordset had thousands of records.  Even though 10 at a time are displayed, unlimited records may be returned.  Someone correct if I am wrong please.

Is it possible to see an example of how to implement CPagination where clicking on next or any page would requery the database for the dataset?  a DAO example would be great. 

Thanks,

R

2

Not really. The demo only brings in a page of records each time.

The following code will add LIMIT and OFFSET to the query criteria:



		$pages=new CPagination(Post::model()->count($criteria));


		$pages->pageSize=Yii::app()->params['postsPerPage'];


		$pages->applyLimit($criteria);


		$posts=Post::model()->with('author')->findAll($criteria);
3

Thanks Qiang, I must of got confused following the code.  I'll have another look at it and try your suggestion.

R

4

I'm still confused with this and can't seem to make it work with DAO, not AR.

If I build a query using something like:

$sql = "SELECT * FROM table LIMIT 10"

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

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

$posts = $command->query($sql);

The adminAction() from the blog tutorial looks like this with no changes:

public function actionAdmin()


	{


		$criteria=new CDbCriteria;





		$pages=new CPagination(Post::model()->count());


		$pages->applyLimit($criteria);





		$sort=new CSort('Post');


		$sort->defaultOrder='status ASC, createTime DESC';


		$sort->applyOrder($criteria);





		$posts=Post::model()->findAll($criteria);





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


			'posts'=>$posts,


			'pages'=>$pages,


			'sort'=>$sort,


		));


	}

How do I change the code above to make it work.

Thanks,

R

5

First, you need to define a COUNT sql and get the result to create $pages.

Second, you  use $pages and $sort to modify your $sql. The former specifies the LIMIT/OFFSET while the latter specifies the ORDER BY.

Third, you execute the query like you did to get $posts.

You can keep the render() statement unchanged.

6

Thanks Qiang.

How do I apply the LIMIT/OFFSET in step 2?  I tried:

$pages->applyLimit(array('OFFSET'=>0, 'LIMIT'=>10)); but it doesn't work.  I also tried passing the values as $pages->applyLimit(0,10) but it didn't work. 

Thanks,

R

7

naah, you can’t use applyLimit because it expects a CDbCriteria object. You can check the implementation of applyLimit to get an idea (very easy. ;))

8

Thanks Qiang.

I overrode the applyLimit method to return the offset/limit and it seems to work fine.  Like anything, it’s easy to understand once you figure it out  :)

Thanks for your patience.

R