如何在 yii2 中计算和分组依据

php mysql yii2

2022-08-30 18:10:50

我想使用 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 中，我会执行以下操作：

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

谢谢！

答案 1

溶液：

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

并添加到模型中，在我的情况下是Lead。public $cnt

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

答案 2

可以通过在选择“查询”中使用 count（） 来获取计数

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

选择查询的各种功能的参考链接