With both sorting and pagination on, your grid may show records in an inconsistent way.
Say you display a list of people and you allow sorting on their attributes. Some attributes are very probably not unique among the shown people, like city or something.
An example: pagination = 10 per page, 15 people in city A. When sorting on city and then moving to the next page, the database query might retrieve some of the people that were already on the first page. (because the database sorts correctly on city, but does not necessarily put all records in the same order within the city A group)
You might think that this happens only when sorting on an attribute that has a low cardinality. Not so. Even if there is only 1 double value for the column you are sorting on, you can get into trouble.
Suppose you sorted on first name. There is an "Anna X" at the bottom of the grid. Navigate to the next page, you would expect "Anna Y" at the top, but you get "Anna X" again. "Anna Y" seems to have disappeared from the records. This happened to me in a production system!
To fix this, you can configure every sort object of your dataproviders to define the possible sorting and include a key so that within the chosen sort, the sorting is still consistent. (explained here: http://www.yiiframework.com/forum/index.php/topic/34554-cgridview-pagination-sorting-problem/page__view__findpost__p__207227)
Here is more elegant solution, an extended [font="Courier New"]CSort[/font]:
class ConsistentSort extends CSort {
public $alwaysAppend;
public function getOrderBy($criteria=null) {
if (isset($this->alwaysAppend)) {
$attribute = $this->alwaysAppend;
if($this->modelClass!==null)
$schema=$this->getModel($this->modelClass)->getDbConnection()->getSchema();
if(isset($schema)) {
if(($pos=strpos($attribute,'.'))!==false)
$attribute=$schema->quoteTableName(substr($attribute,0,$pos)).'.'.$schema->quoteColumnName(substr($attribute,$pos+1));
else
$attribute=($criteria===null || $criteria->alias===null ? $this->getModel($this->modelClass)->getTableAlias(true) : $schema->quoteTableName($criteria->alias)).'.'.$schema->quoteColumnName($attribute);
}
return parent::getOrderBy($criteria) . ',' . $attribute;
}
else
return parent::getOrderBy($criteria);
}
}
[font="Courier New"]ConsistentSort [/font]works exactly like [font="Courier New"]CSort[/font], but adds the property [font="Courier New"]alwaysAppend[/font]:
public function search() {
/* ... build criteria ... */
return new CActiveDataProvider($this, array(
'criteria' => $criteria,
'sort' => array(
'class' => 'ConsistentSort',
'defaultOrder' => array('firstname' => CSort::SORT_ASC),
'alwaysAppend' => 'id_of_this_table'
),
'pagination' => array(
'pageSize' => 20,
),
));
}
In my code I have configured all dataproviders (both CSqlDataProvider and CActiveDataProvider) with this new sort and it works perfectly.
Put here in the hope others may benefit as well…