Weird problem with CActiveDataProvider

Yii 1.1 General Discussion
1

I haven’t come across this before but there seems to be an issue with CActiveDataProvider and the totalItemCount variable when using group by clause in criteria. I have a simple table set up like this…




CREATE TABLE  `IndexTable` (

  `index_sid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `keyword` varchar(120) DEFAULT NULL,

  `position` int(10) unsigned NOT NULL,

  `object_id` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`index_sid`)

) ENGINE=InnoDB CHARSET=utf8;

In it there are a total of 9 records and two distinct object_id values (1 and 2).

When querying it using…




$dp = new CActiveDataProvider('IndexTable',array(

	'criteria'=>array(

	     'group'=>'object_id'

	),

	'pagination'=>array(

	     'pageSize'=>1,

	     'pageVar'=>'page'

	)

));

The item counts are wrong which in turn messes up the pagination in CGridView.




var_dump($dp->totalItemCount) // returns 9 (the total row count of the table

var_dump($dp->itemCount) // return 2 - the correct amount

When used in CGridView, the text at the top displays "Displaying 1-1 of 9 result(s)." and the pagination items go from 1-9 (as expected with the totalItemCount being 9) but it should be pageination of 1-2 and display text being "Displaying 1-1 of 2 result(s)."

I’ve tried debugging this but run out of ideas.

2

Not sure, it may have something to do with this issue…

Attachments Problem with CActiveDataProvider

CActiveDataProvider is still an issue for me…

3

Thanks for the reply. Tried the solution on the link but still not resolved.

I’ve even tried to simplify it further…




$criteria = new CDbCriteria();

$criteria->group = 'object_id';


echo count(IndexTable::model()->findAll($criteria)); // return 2 - CORRECT


$dp = new CActiveDataProvider('IndexTable',array('criteria'=>$criteria));

echo $dp->totalItemCount; // return 9 - INCORRECT (that is the total row count of the table
4

I’ve reported this as a Bug.

5

To confirm, the latest build via SVN has fixed this issue.

6

I’m going to bump this topic.

Either the fix was lost again at some point or not fully implemented. The following code produces the wrong total record count and hence the wrong pagination in CGridView …




        $criteria=new CDbCriteria;


        $criteria->select='ownerName1, SUM(amount) as taxBalance';

        $criteria->group='ownerName1';

        $criteria->compare('ownerName1',$this->ownerName1,true);

        $criteria->compare('taxBalance',$this->taxBalance,true);


        return new CActiveDataProvider(get_class($this), array(

            'criteria'=>$criteria,

            'sort'=>array('defaultOrder'=>'ownerName1 ASC'),

        ));

Also the compare criteria on the SUM(amount) does not work, so CGridView cannot search on taxBalance.

Am I doing something wrong or should I bug report this ?

7

I encountered this problem as well. Any solution?

I have a $criteria->group clause in the criteria passed to the CActiveDataProvider. I see the GROUP BY in the data SQL query, but not in the count SQL query.

As a result the total count is wrong, and pagination is wrong too.

I saw a message saying that there’s a fix in SVN - can anyone instruct me how to get this fix?

Thanks.

8

Noone?

9

At this moment I am having the same issue! The totalItemCount does not use the GROUP BY in the SQL statetement so the total amount is not correct. Anyone knows a fix?

10

Found a fix of another user on this forum :slight_smile:

http://www.yiiframework.com/forum/index.php/topic/20910-cactivedataprovider-cdbcriteria-pagination-count-doesnt-match-number-of-records/page__view__findpost__p__147968

11

Try this code!

It is working fine for me!




public function groupedSearch() {

  $criteria = new CDbCriteria;

  $criteria->select = '…';

  $criteria->with = array(…);

  $criteria->group = '…';

  $dp = new CActiveDataProvider($this, array('criteria'=>$criteria));

  $dp->setTotalItemCount(count($this->findAll($criteria)));

  return $dp;

}