如何推断出城市名称旁边的空缺数量？

Question

I made a sql query that counts of jobs in a particular city

$countVac = Yii::app()->db->createCommand()
        ->select(array('cityId', 'count(*)'))
        ->from('mnk_vacancy')
        ->group('cityId')
        ->queryRow();

My function for deduce city

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

How do I make something like this

Answer 1

public function getCitiesWithVacanciesNumber()
{
    //raw sql
    $sql = '
        SELECT
            c.name city_name,
            COUNT(v.id) vac_num
        FROM cities c
        LEFT JOIN mnk_vacancy v
        ON v.cityId = c.id
        GROUP BY c.name
    ';
    return $this->getDbConnection()->createCommand($sql)->queryAll();
    /*
    AR:
    'vacancies' => array(self::HAS_MANY, 'Vacancy', 'cityId', 'together' => true,)
     */
    $rows = array();
    $cities = self::model()->with('vacancies')->findAll();
    foreach ($cities as $city) {
        $rows[] = array(
            'city_name' => $city->name,
            'vac_num' => count($city->vacancies)
        );
    }
    return $rows;
}