How to count and group by in yii2
When working with ActiveRecord queries, getting aggregation logic right often trips up developers because Yii 2 defaults to a specific execution pattern that differs significantly from raw SQL or older frameworks like Yii 1.x. The core issue arises when you try to apply COUNT() alongside GROUP BY using the standard fluent interface.
If you write code like this:
$leadsCount = Lead::find()
->where(['approved' => 1])
->groupBy(['promoter_location_id', 'lead_type_id'])
->count();
You will generate a subquery that looks like SELECT COUNT(*) FROM (SELECT * FROM lead WHERE approved = 1 GROUP BY promoter_location_id, lead_type_id) c. While this is valid SQL syntax, it is not what you usually want. The database executes the inner query to produce a result set of grouped rows, and then the outer query counts how many distinct groups exist in that result set. This effectively gives you COUNT(DISTINCT ...) behavior, which might not be your target metric if you need specific column aggregations per group.
In Yii 1.x, solving this was straightforward because developers explicitly constructed a CDbCriteria object. You would manually set the select clause to 'COUNT(*) AS cnt' and assign the grouping columns to the group property. This explicit construction ensured the SQL generator prioritized the aggregation logic over the default column selection.
To replicate this control in Yii 2, you need to bypass the automatic query building that happens when you call count() immediately after chaining methods. Instead, construct a fresh ActiveRecord query instance, define your custom select statement including the aggregation function, and then execute it as raw SQL.
Here is how you can achieve the exact output from your original SQL request:
use yii\db\Query;
$query = (new Query())
->select('COUNT(*) AS cnt')
->from(['lead']) // Ensure table alias matches or use explicit alias
->where(['approved' => 1])
->groupBy(['promoter_location_id', 'lead_type_id']);
$rows = $query->all();
// The resulting rows will look like:
// [{ "cnt" => 42 }, { "cnt" => 50 }]
By explicitly setting the select clause to 'COUNT(*) AS cnt', you force the database engine to perform the aggregation on the grouped columns before returning the result set. This mirrors the behavior of the older framework's criteria approach and prevents Yii from wrapping your query in a subquery intended for counting rows, which would otherwise count groups rather than summing a specific metric.
One important detail to watch is table aliasing. When using from(['lead']), Yii assigns an internal alias (often lead or a generated one) to the table. If you are referencing columns in a subquery later, ensure your column names match those aliases. In this specific case, since we are just counting rows within the grouped result, the alias handling is transparent, but it becomes critical if you mix aggregation with other joins or nested subqueries.
The trade-off here is that you lose the convenience of calling count() directly on a model instance. You must manually fetch the array of results and iterate over them. However, this approach provides full fidelity to your SQL requirements without relying on framework assumptions about what count() should return when grouping is involved.
