Yii pagination showing fewer items than available

Question

I have a simple CGridView that is fed from a CActiveDataProvider. At the moment, and I'm not sure how long this has been happening, it does not show all the data items in the view with pagenation enabled.

My header shows "Displaying 1-7 of 9 results" but there are not buttons for more pages. If I set the pageSize of the pagenation property of the data provider to a small number I will eventually get the paging buttons, but it seems to show fewer items on the first page than the second page. For example, if I set the pageSize of the CActiveDataProvider to 3 I get 2,2,3 (items on each page) instead of 3,3,1 as I might expect.

If I set the pageSize to anything between 9 and 11 inclusive there are items I cannot see because I only get one page and not all of the items show up ("1-6 of 9" if I set the pageSize to 9).

$criteria=new CDbCriteria;
$criteria->with = array('registrations'); 
$criteria->addCondition('registrations.id IS NOT NULL');
$criteria->addCondition('registered = false');
$criteria->together = true;
$dataProvider = new CActiveDataProvider('Skier', array('criteria'=>$criteria));

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$dataProvider,
    'columns'=>array(
        array('name'=>'fullname', 'header'=>'Name'),
        array(
            'name'=>'programs_names',
            'header'=>'Programs',
            'value'=>'$data->programs_names',
        ),
        <More items here>
    )
));

Anyone have any idea what would cause the pagenation to be so wonky?

Edit: Also, changing the CActiveDataProvider to a CArrayDataProvider works correctly and I get 9 of 9 results. This will work for now because I have small data sets, but I would rather figure out what the problem might be.

$dataProvider = new CArrayDataProvider(Skier::model()->findAll($criteria));

Answer 1

If you are using a complex query try this one

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

$sql='SELECT * FROM tbl_user';

$dataProvider=new CSqlDataProvider($sql, array(
    'totalItemCount'=>$count,
    'sort'=>array(
        'attributes'=>array(
             'id', 'username', 'email',
        ),
    ),
    'pagination'=>array(
        'pageSize'=>10,
    ),
));
// $dataProvider->getData() will return a list of arrays.

Then you can pass your $dataProvider to your CGridView

Further docs on using custom query as dataProvider, check here.

Answer 2

I had a problem like this. it was due to duplicate entries (primary keys) in the database.

Answer 3

To show exactly ALL records, 'count rows' and then 'supply $countResult to pageSize', for example

First count rows like (this is an example, 'User' = 'your model name') )

$countResult = User::model()->countByAttributes(array('record_name' => $matched_record_name));

OR

$countResult = User::model()->count($condition);

And then supply $countResult to pageSize

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

Answer 4

Try to add the following to "Skier" model's search method:

$criteria->group = "id";

Or:

$criteria->group = "t.id";

Answer 5

I had to put $criteria->with and its together=true inside an if():

if( (int) $this->categoria_id > 0) {
    $criteria->with = Array (
        'categorie'=> Array (
            'together' => true,
        )
    );
    $criteria->compare ('categorie.id', $this->categoria_id);
}

otherwise CActiveDataProvider pagination with together=true and no parameter passed returns a wrong count