如何在yii2中进行计数和分组 [英] How to count and group by in yii2

问题描述

我想使用yii2生成以下查询:

I would like to generate following query using yii2:

SELECT COUNT(*) AS cnt FROM lead WHERE approved = 1 GROUP BY promoter_location_id, lead_type_id

我尝试过:

$leadsCount = Lead::find()
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->count();

哪个生成以下查询:

SELECT COUNT(*) FROM (SELECT * FROM `lead` WHERE approved = 1 GROUP BY `promoter_location_id`, `lead_type_id`) `c`

在yii 1.x中，我将执行以下操作:

In yii 1.x I would've done the following:

$criteria = new CDbCriteria();
$criteria->select = 'COUNT(*) AS cnt';
$criteria->group = array('promoter_location_id', 'lead_type_id');

谢谢！

推荐答案

解决方案:

$leadsCount = Lead::find()
->select(['COUNT(*) AS cnt'])
->where('approved = 1')
->groupBy(['promoter_location_id', 'lead_type_id'])
->all();

并将public $cnt添加到模型中，在我的情况下为Lead.

and add public $cnt to the model, in my case Lead.

正如Kshitiz所说，您也可以只使用yii\db\Query::createCommand().

As Kshitiz also stated, you could also just use yii\db\Query::createCommand().

这篇关于如何在yii2中进行计数和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！