dataprovider total results different from actual number

Yii 1.1 General Discussion
1

I was having a problem using count() and $criteria->group with dataproviders as follows:

In database:

LIST table has 2 rows

ID NAME

1 Sample List #1

2 Sample List #2

LISTITEM table has 4 rows

LIST_ID ITEM_ID

1 9

1 10

2 11

2 12

In controller:


$dataProvider=new CActiveDataProvider('list', array(

            'criteria'=>array(

		'select'=> 'COUNT(list_id) AS numItems, t.name',

		'join'=>'JOIN listitem i ON (i.list_id=t.id)',

		'group'=>'t.id',

            ),

        ));

In view:




<?php $this->widget('zii.widgets.CListView', array(

	'dataProvider'=>$dataProvider,

	'itemView'=>'_view',

)); ?>

In the view, the CListView pager would display the correct number of rows (2) with the correct number of items for each list (2). However, the total number of results displayed in the pager summary is 4. Digging into the logs shows that the group by clause is being left out of the sql statement when the rows were counted. I did some searching and it seemed like the problem was fixed by the yii developers, but for whatever reason it’s still not working for me.

As a work around I added


$dataProvider->totalItemCount =count($dataProvider->keys);

to set the correct number of rows. Is there a better way to do this?

2

Try it this way




$dataProvider=new CActiveDataProvider('list', array(

            'criteria'=>array(

                'select'=> 'COUNT(t.id) AS numItems, t.name, i.list_id',

                'join'=>'JOIN listitem i ON (i.list_id=t.id)',

                'group'=>'i.list_id',

            ),

        ));

Hopefully this will give you the results you’re looking for. Make sure you include the column in the SELECT statement that you are going to GROUP BY - in this case ‘i.list_id’. For more info on grouping with aggregate functions, check out this tutorial.