[SOLVED] Change Sql request used for pagination

Yii 1.1 General Discussion
1

Hi!

I’am trying to use the pagination with Yii and everything is working fine! But (there is always a but ^^) the request executed to get the number of object is not optimized:

In my Controller I have the following code:




<?php

$criteria = new CDbCriteria(array(

                        'with' => array('series', 'author', 'stars'),

                        'condition' => "etat = " . 1,

                ));

$dataProvider = new CActiveDataProvider(News::model(), array(

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                        'criteria' => $criteria,

                ));

?>

And the request performed for the pagination by CPagination is:


SELECT COUNT(DISTINCT `t`.`id`) FROM `news` `t` LEFT OUTER JOIN `news_serie` `series_series` ON (`t`.`id`=`series_series`.`id_news`) LEFT OUTER JOIN `serie` `series` ON (`series`.`id`=`series_series`.`id_serie`) LEFT OUTER JOIN `user` `user` ON (`t`.`id_user`=`user`.`id`) LEFT OUTER JOIN `news_stars` `stars_stars` ON (`t`.`id`=`bonhommes_bonhommes`.`id_news`) LEFT OUTER JOIN `stars` `stars` ON (`stars`.`id`=`stars`.`id_stars`) WHERE (etat = 1)

This request is way to complicated in this case and I would Like to use:


SELECT COUNT(DISTINCT `t`.`id`) FROM `news` `t`  WHERE (etat = 1)

So my question is: How could i change the request to get the total number of news?

2

For the total item count of the data provider: http://www.yiiframework.com/doc/api/1.1/CDataProvider#totalItemCount-detail




<?php

$totalCount = Yii::app()->db->createCommand("SELECT COUNT(DISTINCT `t`.`id`) FROM `news` `t`  WHERE (etat = 1)")->queryScalar();


$dataProvider = new CActiveDataProvider(News::model(), array(

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                        'criteria' => $criteria,

                        'totalItemCount' => $count,

                ));

?>
3

Thanks it’s working perfectly!