Cgridview - Count() Madness

Yii 1.1 General Discussion
1

I have a cGridview to display download history for site members. My download_history table has a number of IDs to reference data in other tables, eg. member_name. Four related tables in total. Thus I have set up relations for these tables in the model:




			'members' =>array(self::BELONGS_TO, 'Members', '', 'on' => 't.download_user_auth_id = members.member_auth_id'),

			'releases' => array(self::BELONGS_TO, 'Releases', 'download_release_id'),

			'tracks' => array(self::BELONGS_TO, 'Tracks', 'download_track_id'),

			'labels' => array(self::BELONGS_TO, 'Labels', 'download_label_id'),

I am using the model search() for the grid and because I want to display things like member_name, release_name etc. I am using $criteria->with to join these tables.

The main query executes nicely in 0,015 sec., however the count query is simply insane, taking almost 30 seconds to execute… and no wonder when you take a look at it:




SELECT COUNT(DISTINCT `t`.`download_id`) FROM

`download_history` `t`  LEFT OUTER JOIN `members` `members` ON

(t.download_user_auth_id = members.member_auth_id)  LEFT OUTER JOIN

`releases` `releases` ON (`t`.`download_release_id`=`releases`.`id`)  LEFT

OUTER JOIN `tracks` `tracks` ON (`t`.`download_track_id`=`tracks`.`id`) 

LEFT OUTER JOIN `labels` `labels` ON

(`t`.`download_label_id`=`labels`.`id`)

How can I make Yii ignore the joins for the count query, since I want all records from my download_history table to be counted and "SELECT count(*) FROM download_history" is sufficient and almost instant?

2

Ok, the solution is quite simple. We can bypass the count query by adding ‘totalItemCount’ to our CActiveDataProvider and performing our own count query:




return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'totalItemCount'=>$count,

			'pagination'=>array('pageSize'=>20),

			'sort' => array(

                            'defaultOrder' => 't.download_id DESC',

                       ),

		));