如何在yii中使用cdbCriteria中的SUM？

b1uwtaje 于 2022-11-09 发布在其他

关注(0)|答案(3)|浏览(144)

我有这个代码，这是显示所有的每日退订用户。

public function getdailyUnSubs($modelName,$op){

    $currentDate = date('Ymd');
    $criteria = new CDbCriteria;
    $criteria->select = 'app_unsubs,sms_unsubs';

    $criteria->order = 'date DESC';
    $criteria->limit = 1;
    $criteria->offset = 0;
    $Subscriber = $modelName::model()->find($criteria);

    $totalunSubs = $Subscriber['app_unsubs']+$Subscriber['sms_unsubs'];

    return $totalunSubs;
}

但现在我想得到所有未订阅的用户到目前为止。我想要的总和所有未订阅的用户。
这是我的代码，但它不工作.

public function getTotalUnSubs($modelName,$op){

    $criteria = new CDbCriteria;
    $criteria->select ='sum(app_unsubs) as appunsubs, sum(sms_unsubs) as smsunsubs';

    $Subscriber = $modelName::model()->find($criteria);

    $totalunSubs = $Subscriber['appunsubs']+$Subscriber['smsunsubs'];

    return $totalunSubs;
}

这是说appunsubs和smsunsubs是没有定义的。
即使我将代码更改为
$criteria->select ='(sum(app_unsubs)+ sum(sms_unsubs)) as totalunsubs';
和
$totalunSubs = $Subscriber['totalunsubs'];
它仍然说totalunsubs是未定义的。

yii

来源：https://stackoverflow.com/questions/37525477/how-to-use-sum-in-cdbcriteria-in-yii

3条答案

按热度按时间

nkcskrwz1#

您没有定义appunsubs和smsunsubs，因为在您的$modelName模型类中没有类似的字段。要获取数据，您可以基于条件创建查询，并使用Yii::app()->db->createCommand($sql)->queryRow()执行此查询。请查看以下代码：

public function getTotalUnSubs($modelName,$op)
{
    $criteria = new CDbCriteria;
    $criteria->select ='sum(app_unsubs) as appunsubs, sum(sms_unsubs) as smsunsubs';

    //creating proper SQL
    $sql = Yii::app()->db->commandBuilder->createFindCommand($modelName, $criteria)->getText();

    //fetching data based on created SQL stored in $sql variable
    $Subscriber = Yii::app()->db->createCommand($sql)->queryRow();

    $totalunSubs = $Subscriber['appunsubs']+$Subscriber['smsunsubs'];

    return $totalunSubs;
}

赞(0）回复(0）举报 2022-11-09

u0sqgete2#

我认为您必须在查询中设置Group By。
所以代码现在是这样的。

public function getTotalUnSubs($modelName,$op){

    $criteria = new CDbCriteria;
    $criteria->select ='sum(app_unsubs) as appunsubs, sum(sms_unsubs) as smsunsubs';
    $criteria->group='table primary key';

    $Subscriber = $modelName::model()->find($criteria);

    $totalunSubs = $Subscriber['appunsubs']+$Subscriber['smsunsubs'];

    return $totalunSubs;
}

赞(0）回复(0）举报 2022-11-09

cbjzeqam3#

简单的解决方案是将公共或受保护的变量名作为sum添加到模型中。我建议将其作为“子模型”使用...

class Summary extends CActiveRecord {
   public $total;
}

class Item extends Summary {
   ...
}

然后您可以在criteria中自由使用count或sum，它们将作为变量出现在使用find、findAll等检索模型中。

$criteria = new CDbCriteria();
$criteria->select = "count(id) as total,type,lang,source";
$criteria->group = "lang,source,type"; 
$result = Item::model()->findAll($criteria);

结果为：（举例说明）

array(1) {
  [0]=>
  object(Item)#48 (12) {
    ["total"]=>
    string(2) "50"

这些子模型非常适合保存函数、关系和其他内容，就好像类需要从Gii自动生成一样，您只需要将extends CActiveRecord替换为extends SubmodelName...
此外，在这些年里，我开发了Base（模型），设置了我通常需要的一切（登录用户的user_id，检查日期的格式是否正确，等等）

赞(0）回复(0）举报 2022-11-09

我来回答

如何在yii中使用cdbCriteria中的SUM？

3条答案

相关问题

热门标签

最新问答