How to count and group by in yii2

In Yii2, you can use the count() and groupBy() methods in the query builder to count and group the results of a query, respectively.

Here is an example of how to use these methods to count the number of users grouped by their role:

Example of counting the number of users grouped by their role in yii2

$users = (new \yii\db\Query())
    ->select(['role', 'COUNT(*) as count'])
    ->from('user')
    ->groupBy(['role'])
    ->all();

In this example, count(*) is used to count the number of rows for each role, and the results are grouped by the role column. The query returns an array of arrays, each containing the role and count for that group.

You can also use the createCommand() method to execute the SQL statement and get the count of grouped data.

Example of using the createCommand() method to execute the SQL statement and get the count of grouped data in yii2

$count = Yii::$app->db->createCommand("SELECT role, COUNT(*) as count FROM user GROUP BY role")->queryAll();

Note: When using raw SQL, always use parameter binding (e.g., :role) to prevent SQL injection.

You can also use the count() method to get the count of rows in a table.

Example of using count() method to get the count of rows in a table in yii2

$count = (new \yii\db\Query())->from('user')->count();

The count() method returns a single integer representing the total number of rows. In contrast, the grouped queries above return an array of associative arrays, where each element contains the group key and its corresponding count.