How to display the number of (for example) vacancies next to the name of the city?

Question

Yii

How to make such a thing: there is a list of cities and next to it you need to display the number of vacancies in this city
. For example: Moscow 34 (vacancies), St. Petersburg 23 (vacancies), etc. I wrote a sql query that displays the number of vacancies in the database by city id, but I don’t know how to describe it in yii
Request:

SELECT cityId, COUNT(*) FROM mnk_vacancy GROUP BY city_id

And here is a function that displays a list of cities

public function getCityList()
    {
        $cityList = $this->findAll(array('order' => 'name'));
        $cityArray = CHtml::listData($cityList, 'id', 'name');
        return $cityArray;
    }

}

Answer 1

$connection=Yii::app()->db; 
command=$connection->createCommand($sql);
$rowCount=$command->execute(); // execute the non-query SQL
$dataReader=$command->query(); // execute a query SQL