How to count and group by in yii2

Question

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

I have tried:

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

Which generates this query:

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

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'); 

Thanks!

Answer 1

Solution:

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

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

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