Pagination solution [simple and complex ways]

Yii 1.1 Tips, Snippets and Tutorials
1

First, I’m new in Yii, so if I’m not with the framework standards, please notify me!

I had some problem with pagination and searching for it I found a lot of explanations but none worked well. Messing around I made to solutions:

1 – pagination for simple query

2 – Pagination for complex queries (multiple tables etc)

[i]First, define a new param at config/main.php. Find it at last lines


'params'=>array(

        'listPerPage'=> 10, // <-- insert this line with the number you prefer

[/i]

[size="4"]1. Simple way[/size]

CONTROLLER

// just an example to “illustrate” the tutorial, make the proper adaptations


$criteria = new CDbCriteria();

       $criteria->condition = 'collumnName1 = :id';

       $criteria->order = 'id DESC';

       $criteria->params = array (':id'=>$id);

       

        $item_count = ModelNameX::model()->count($criteria);

                

        $pages = new CPagination($item_count);

        $pages->setPageSize(Yii::app()->params['listPerPage']);

        $pages->applyLimit($criteria);  // the trick is here!

        

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

    			'model'=> ModelNameX::model()->findAll($criteria), // must be the same as $item_count

                'item_count'=>$item_count,

                'page_size'=>Yii::app()->params['listPerPage'],

                'items_count'=>$item_count,

                'pages'=>$pages,

	));

VIEW


$data = array();

foreach($model as $m){  // loop to get the data (this is different from the complex way)

$data[] = $m->attributes;

}


// the pagination widget with some options to mess

$this->widget('CLinkPager', array(

	    'currentPage'=>$pages->getCurrentPage(),

	    'itemCount'=>$item_count,

	    'pageSize'=>$page_size,

	    'maxButtonCount'=>5,

	    //'nextPageLabel'=>'My text >',

	    'header'=>'',

        'htmlOptions'=>array('class'=>'pages'),

	));

[size="4"]2. Complex Way[/size]

CONTROLLER

// just an example to “illustrate” the tutorial, make the proper adaptations


$page = (isset($_GET['page']) ? $_GET['page'] : 1);  // define the variable to “LIMIT” the query

        


        $query1 = Yii::app()->db->createCommand() //this query contains all the data

        ->select(array('t2.*'))

        ->from(array('table1Name t1', 'table2Name t2'))

        ->where('t1.collumName = '.$id.' AND t2. collumName = t1. collumName and t2. collumName > now()')

        ->order('t2. collumName DESC')

        ->limit(Yii::app()->params['listPerPage'], $page-1) // the trick is here!

        ->queryAll();

        

        $item_count = Yii::app()->db->createCommand() // this query get the total number of items,

        ->select('count(*) as count')

        ->from(array('table1Name t1', 'table2Name t2')) // must be the same as $query1 !!!!

        ->where('t1. collumName = '.$id.' AND t2.id = t1. collumName and t2. collumName > now()') // must be the same as $query1 !!!!

        ->queryAll(); // do not LIMIT it, this must count all items!


// the pagination itself

        $pages = new CPagination($item_count);

        $pages->setPageSize(Yii::app()->params['listPerPage']);


// render

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

            'query1'=>$query1,

            'item_count'=>$item_count,

            'page_size'=>Yii::app()->params['listPerPage'],

            'pages'=>$pages,

		));

VIEW


foreach($query1 as $q){} // loop to get data


// the pagination widget with some options to mess

$this->widget('CLinkPager', array(

                            	    'currentPage'=>$pages->getCurrentPage(),

                            	    'itemCount'=>$item_count,

                            	    'pageSize'=>$page_size,

                            	    'maxButtonCount'=>5,

                            	    //'nextPageLabel'=>'My text >',

                            	    'header'=>'',

                                    'htmlOptions'=>array('class'=>'pages'),

                            	));
2

Nice tutorial. Thanks!

3

I tried out the complex way and ended up getting the following error: Fatal error: Unsupported operand types in …\Yii\framework\web\CPagination.php on line 144. Not able to figure out what went wrong. Any help?

4

Thank you! Keep em coming.

5

  • Are you using Yii 1.1.8? I made this script in it.

  • This error is happening in controller or in view?

6

Hi! I have the same issue. It’s Yii 1.1.8 and this error is happening in the view.

I’ve changed queryAll() to queryRow() at $item_count and gave to a widget this ‘itemCount’=>$item_count[‘count’] (instead of ‘itemCount’=>$item_count).

It has fixed an error, but only at the first page. When I tried to go to another page the error comes up again.

Also, the first page gives an error if there’s “…&page=1” in the address.

PS it’s complex way

PPS i’m new to Yii :)

upd Just updated to 1.1.9, now it’s the same error at line 152

7

Sorry, just now I saw your reply. Copy/Paste the error line here. This code still working like a charm for me here.

8

The "Complex Way" above simply does not work. Try this:




  $sql = 'SELECT t2.* FROM table1Name t1, table2Name t2 WHERE t1.collumName = '.$id.' AND t2. collumName = t1. collumName and t2. collumName > ' . new CDbExpression('now()');


  $count_query = 'select count(*) FROM table1Name t1, table2Name t2 WHERE t1.collumName = '.$id.' AND t2. collumName = t1. collumName and t2. collumName > ' . new CDbExpression('now()');


  $item_count = Yii::app()->db->createCommand($count_query)->queryScalar();


  $dataProvider=new CSqlDataProvider($sql, array(

    'keyField'=>'id',

    'totalItemCount'=>$item_count,

    'pagination'=>array(

      'pageSize'=>Yii::app()->session['pageSize'],

    ),

  ));


  $model = $dataProvider->getData();


  $this->widget('CLinkPager',array(

    'pages'=>$dataProvider->pagination));


  foreach($model as $val) {

    // do stuff

  }
9

This is great thanks!!!

but why did you include $item_count twice in your simple version render parameters?

10

Great tutorial work, i have implemented the Simple way. I didn’t understand why have you used the $item_count twice. I think there is no use of sending the parameter $item_count to the view twice.

11

I had also followed the complex way for the pagination but it doesn’t splits my items into multiple pages, it just puts all the items onto one page, although it gives the Pager link below all the items.

Thanks in advance.

12

The complex way added by alexFidelis is having some issue, but I am unable to found and fix the problem. With the help of solution added by Backslider I achieved the custom pagination with the following code.


public function actionIndex()

{

    $conditions = array();

    $params = array();

    $model=new TransactionCategories();

    if(isset($_GET['TransactionCategories'])){

        $model->attributes = $_GET['TransactionCategories'];

        if (!empty($_GET['TransactionCategories']['name'])){

            $conditions = 'tc.name like "%' . $_GET['TransactionCategories']['name'] . '%"';

            $params = array (':name' => $_GET['TransactionCategories']['name']);

        }

    }

    $transactionCategoriesCount = Yii::app()->db->createCommand()

    ->select('count(*) as count')

    ->from('transaction_categories tc')

    ->where($conditions, $params)

    ->queryScalar();

    $transactionCategoriesSQL = Yii::app()->db->createCommand()

    ->select('tc.id, tc.created, tc.name, tc.transaction_count')

    ->from('transaction_categories tc')

    ->where($conditions, $params);

    $transactionCategoriesDP=new CSqlDataProvider($transactionCategoriesSQL->text, array(

            'totalItemCount'=>$transactionCategoriesCount,

            'sort'=>array(

                    'attributes'=>array(

                            'tc.id', 'tc.created', 'tc.name',

                    ),

            ),

            'pagination'=>array(

                    'pageSize'=> Yii::app()->params['listPerPage'],

            ),

    ));

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

            'transactionCategoriesDP' => $transactionCategoriesDP,

            'transactionCategoriesCount' => $transactionCategoriesCount,

            'model' => $model

    ));

}

View Code


<?php 

$transactionCategories = $transactionCategoriesDP->getData();

$form=$this->beginWidget('CActiveForm', array(

		'action'=>CHtml::normalizeUrl(array('transactionCategories/gupdate')),

		'id'=>'transaction-categories-form',

		'enableAjaxValidation'=>false,

));

?>

<div class="grid-view">

	<table class="items">

		<thead>

			<tr>

				<th>&nbsp;</th>

				<th><?php echo $transactionCategoriesDP->getSort()->link('tc.id', 'ID');?>

				</th>

				<th><?php echo $transactionCategoriesDP->getSort()->link('tc.created', 'Created');?>

				</th>

				<th><?php echo $transactionCategoriesDP->getSort()->link('tc.name', 'Name');?>

				</th>

				<th>Actions</th>

			</tr>

		</thead>

		<tbody>

			<?php 

			if (!empty($transactionCategories)){

	foreach($transactionCategories as $transactionCategorie){

?>

			<tr class="odd">

				<td><?php echo $form->checkBox($model,'id[]', array('value' => $transactionCategorie['id'])); ?>

				</td>

				<td><?php echo $transactionCategorie['id'];?></td>

				<td><?php echo $transactionCategorie['created'];?></td>

				<td><?php echo $transactionCategorie['name'];?></td>

				<td><?php 

				echo CHtml::link('Update', array('/transactionCategories/update/id/' . $transactionCategorie['id']));

				echo CHtml::linkButton('Delete', array(

			         'submit'=>array('transactionCategories/Delete', 'id' =>$transactionCategorie['id']),

			         'params'=>array('id' =>$transactionCategorie['id']),

					'confirm'=>"Are you sure want to delete this data ?"

				));

			?>

				</td>

			</tr>

			<?php	

	}

}else{

?>

			<tr class="">

				<td colspan="100">Sorry no records found</td>

			</tr>

			<?php 

}

?>

		</tbody>

	</table>

</div>

<div class="row buttons">

	<?php echo CHtml::dropDownList('action', null, array('' => 'Select', 'delete' => 'Delete')); ?>

	<?php echo CHtml::submitButton('Update'); ?>

</div>

<?php $this->endWidget(); ?>

<?php

$this->widget('CLinkPager', array(

                                   'itemCount'=> $transactionCategoriesCount,

                                   'pageSize'=> Yii::app()->params['listPerPage'],

                                   'maxButtonCount'=> 10,

                                   'nextPageLabel'=>'Next >>',

                                   'header' => 'Go to page::',

                                    //'htmlOptions'=>array('class'=>'pages'),

                                ));

?>
13

Just a little improvement about the semantic, what a lot of you call $model should instead be called $objects, unless you really retrieve Models from your DB :D

14

backslider’s solution is just cool. Straight and sweet. However, I made a variant of it after seeing his. I used CArrayDataProvider instead of the CSqlDataProvider he used. The former lets you use either raw sql or AR objects. This is useful if your query returns AR objects (e.g. from a get method) or if your views, especially the partials are expecting AR objects and not datareader arrays as when using raw sql. See AR example like so

In a contoller method




    $pageSize=5; 

    $products = $this->doSearch($searchterm); //returns AR objects

    $count = count($products);

             


             $dataProvider= new CArrayDataProvider($products, array(

                'sort'=>array('attributes'=>array('product_id')),   //you can sort here if objects not sorted

                'pagination'=>array('pageSize'=>$pageSize),

              ));

            

             $models = $dataProvider->getData();


             

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

                    'models' => $models,

                    'dataProvider' => $dataProvider, 

                    'itemCount' => $count,

             ));

And on the view




  foreach($models as $model){

     ...

     ...

   }


   $this->widget('CLinkPager', array(

                                'pages'=>$dataProvider->pagination,

                    ))

Hope this helps somebody.

Cheers

15

Great tutorial. Has anyone tried pagination after advanced search. The first page seems to load properly but it doesn’t load from the second page onwards.

16

You don’t have to add a separate application parameter to manage widget settings.

To modify widgets app-wide, use the CWidgetFactory component:




Yii::app()->widgetFactory->widgets["CLinkPager"]["pageSize"] = 42;

You could even extend and override the component to return dynamic configurations, e.g. those specified by the user.

17

Thanks, great tutorial

18

The problem with this solution is that it loads ALL the records in the database into memory (and then sorts them in php, which will likely be slower than sorting in the database). If your table has lots of rows, this can be very slow or might even hit the memory limit. You should use a CActiveDataProvider, so that you only load the current page’s worth of data from the database. If you need the flexibility to use either raw sql or AR objects, you can use either CSqlDataProvider or CActiveDataProvider as needed, that’s why they’re descendants of the same CDataProvider class.

19

Thanks buddy this code saves my job. :D