CSqlDataProvider复杂查询和totalItemCount

Question

我有一个使用CSqlDataProvider的非常复杂的查询，我试图让分页totalItemCount工作，但它返回错误的计数。我试着创建第二个SQL来计算条目，但这真的很难，有人能帮我吗？

CSqlDataProvider文档：http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

控制器代码：

    $dayofweek = date('w');     

    $sql = "( SELECT `profile`.`id` as profile_id, `profile`.`name` as profile_name, `events_reminder`.`id`,
                CASE WHEN weekday >= $dayofweek
                    THEN 
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL weekday - $dayofweek DAY), ' ', event_time ), '%d/%m/%Y %H:%i')
                    ELSE
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL 7 - $dayofweek + weekday DAY), ' ', event_time ), '%d/%m/%Y %H:%i')
                    END as revent_datetime,
                CASE WHEN weekday >= $dayofweek
                    THEN 
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL weekday - $dayofweek DAY), ' ', list_time ), '%d/%m/%Y %H:%i')
                    ELSE
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL 7 - $dayofweek + weekday DAY), ' ', list_time ), '%d/%m/%Y %H:%i')
                    END as rlist_datetime
                FROM events_reminder
                LEFT OUTER JOIN `profiles` `profile` ON (`events_reminder`.`profile_id`=`profile`.`id`)
                HAVING revent_datetime NOT IN (SELECT DATE_FORMAT(event_datetime, '%d/%m/%Y %H:%i') FROM events WHERE `events`.`profile_id`=`events_reminder`.`profile_id`) )
            UNION ALL
            ( SELECT `profile`.`id` as profile_id, `profile`.`name` as profile_name, `events_reminder`.`id`,
                CASE WHEN weekday >= $dayofweek
                    THEN 
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL weekday - $dayofweek + 7 DAY), ' ', event_time ), '%d/%m/%Y %H:%i')
                    ELSE
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL 7 - $dayofweek + weekday + 7 DAY), ' ', event_time ), '%d/%m/%Y %H:%i')
                    END as revent_datetime,
                CASE WHEN weekday >= $dayofweek
                    THEN 
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL weekday - $dayofweek + 7 DAY), ' ', list_time ), '%d/%m/%Y %H:%i')
                    ELSE
                        DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL 7 - $dayofweek + weekday + 7 DAY), ' ', list_time ), '%d/%m/%Y %H:%i')
                    END as rlist_datetime
                FROM events_reminder
                LEFT OUTER JOIN `profiles` `profile` ON (`events_reminder`.`profile_id`=`profile`.`id`)
                HAVING revent_datetime NOT IN (SELECT DATE_FORMAT(event_datetime, '%d/%m/%Y %H:%i') FROM events WHERE `events`.`profile_id`=`events_reminder`.`profile_id`) )
                ORDER BY revent_datetime";

    $count=Yii::app()->db->createCommand($sql)->queryScalar();

    $dataProvider=new CSqlDataProvider($sql, array(
        'totalItemCount'=>$count,
    ));

    $this->render('index',array(
        'dataProvider'=>$dataProvider,
    ));

Answer 1

queryScalar所做的是：

执行SQL语句并返回data第一行中第一列的值。

因此，要使用它来获取count，您必须在sql查询中添加一个count(*)，也就是在查询的开头，如下所示：

SELECT count(*), `profile`.`id` as profile_id, `profile`.`name` ...

更新：

由于您使用的是UNION，因此必须将count(*)移到外部，并提供别名：

SELECT count(*) from (((select ...) UNION ALL (select ...)) as alias)

对于您的确切情况，您可以这样做：

$count_sql='SELECT count(*) from (('. $sql .') as alias)';
$count=Yii::app()->db->createCommand($count_sql)->queryScalar();