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));

ever get this resolved? I am having same issue. I suspect it has something to do with the underlying query mechanism doing two different counts and because of some edge case or peculiarity with the data those counts end up not matching. — SoldierOfFortran, Jun 18 '14 at 18:09
Hi, did you find any solution with CActiveDataProvider ? I have facing the similar issue and I have no idea what is going wrong here. — Mohit Bhansali, Dec 16 '15 at 10:52

score 1 · Answer 1 · answered Dec 05 '13 at 04:47

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.

answered Dec 05 '13 at 04:47

Neeraj Kumar

1,058
1
9
22

I can try something like that, but it seems like, if it works, it is just working around some other bug with CActiveDataProvider using a more complex SQL query rather than using the models.I have never seen this before, and have a lot of grid views with data providers, so I assume it is a problem with my query or model, but I was hoping someone could shed some light on what could cause this strange behaviour. – Poco Dec 05 '13 at 16:12

score 1 · Answer 2 · answered Dec 08 '13 at 18:49

1

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

answered Dec 08 '13 at 18:49

cnlevy

10,630
3
20
21

I have the same issue and this wasn't the case in my project. I had 2 joins in my search and each model have a unique and one primary key and it still doesn't work. I suspect it might be the framework and bug. – Gajen Sunthara Aug 13 '15 at 14:16

Gianpaolo Scrigna · Answer 3 · 2016-04-03T09:24:40.643

1

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

edited Apr 03 '16 at 09:24

answered Apr 02 '16 at 10:28

Gianpaolo Scrigna

720
6
22

score 0 · Answer 4 · answered Feb 17 '16 at 00:39

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),
                )),

score 0 · Answer 5 · answered Feb 10 '17 at 15:34

0

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

$criteria->group = "id";

Or:

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

answered Feb 10 '17 at 15:34

Torben

479
6
16

Yii pagination showing fewer items than available

5 Answers5