How to count the number of records using ActiveQuery?

Question

Yii

There is an employees table , it has a department_id field . The relationship is declared in the Employees model :

public function getDepartment()
    {
        return $this->hasOne(Management::className(), ['id' => 'department_id']);
    }

The management table stores department names ( value field ). You need to count the number of employees for each department . The result is an array ;.

In SQL it looks like this:

SELECT `management`.`value`, COUNT(*) FROM `employees` LEFT JOIN `management` ON `employees`.`department_id` = `management`.`id` GROUP BY `employees`.`department_id`

Is it possible to do this through Employees::find() without additional manipulations (I mean first get the number of employees for each department_id , and then get the name of the department through department_id ) and how to do it?

DECISION:

ArrayHelper::map(Employees::find()
            ->select(['COUNT(*) AS count', 'value'])
            ->joinWith('department')
            ->groupBy('department_id')
            ->createCommand()
            ->queryAll(), 'value', 'count');

Answer 1

Try like this:

Employees::find()
    ->select(['COUNT(*) AS depcount'])
    ->joinWith('department')
    ->groupBy('department_id')
    ->all();

just add it to the model,
or if you don't need an object, then it's easier

Employees::find()
    ->select(['COUNT(*) AS depcount'])
    ->joinWith('department')
    ->groupBy('department_id')
    ->createCommand()
    ->queryAll();