dataprovider total results different from actual number

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

In database:

LIST table has 2 rows


1 Sample List #1

2 Sample List #2

LISTITEM table has 4 rows


1 9

1 10

2 11

2 12

In controller:

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


		'select'=> 'COUNT(list_id) AS numItems,',

		'join'=>'JOIN listitem i ON (',




In view:

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



)); ?>

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?

Try it this way

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


                'select'=> 'COUNT( AS numItems,, i.list_id',

                'join'=>'JOIN listitem i ON (',




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.