innerJoinWith and table alias in select

[lynicidn]

i have 3 tables:

CREATE TABLE `net_city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) DEFAULT NULL,
  `name_ru` varchar(100) DEFAULT NULL,
  `name_en` varchar(100) DEFAULT NULL,
  `region` varchar(2) DEFAULT NULL,
  `postal_code` varchar(10) DEFAULT NULL,
  `latitude` varchar(10) DEFAULT NULL,
  `longitude` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`),
  KEY `name_en` (`name_en`),
  KEY `name_ru` (`name_ru`),
  CONSTRAINT `FK_net_city_net_country_id` FOREIGN KEY (`country_id`) REFERENCES `net_country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=389634 DEFAULT CHARSET=utf8;

CREATE TABLE `net_country` (                                                                                                                         
  `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                              
  `name_ru` varchar(100) DEFAULT NULL,                                                                                                               
  `name_en` varchar(100) DEFAULT NULL,
  `code` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `name_en` (`name_en`),
  KEY `name_ru` (`name_ru`)
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8;

CREATE TABLE `net_city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) DEFAULT NULL,
  `name_ru` varchar(100) DEFAULT NULL,
  `name_en` varchar(100) DEFAULT NULL,
  `region` varchar(2) DEFAULT NULL,
  `postal_code` varchar(10) DEFAULT NULL,
  `latitude` varchar(10) DEFAULT NULL,
  `longitude` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`),
  KEY `name_en` (`name_en`),
  KEY `name_ru` (`name_ru`),
  CONSTRAINT `FK_net_city_net_country_id` FOREIGN KEY (`country_id`) REFERENCES `net_country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=389634 DEFAULT CHARSET=utf8;

and i do 2 actions:

      public function actionSearchCountry($q) {
        $this->ajaxOnly();
        return NetCountry::find()
            ->select(['country' => 'name_ru'])
            ->where(['like', 'name_ru', $q])
            ->innerJoinWith('clinics')
            ->asArray()->all();
    }

    public function actionSearchCity($q) {
        $this->ajaxOnly();
        return NetCity::find()
            ->select(['city' => 'name_ru'])
            ->where(['like', 'name_ru', $q])
            ->innerJoinWith('clinics')
            ->asArray()->all();
    }

    private function ajaxOnly()
    {
        if (!Yii::$app->request->isAjax) {
            throw new HttpException(404);
        } else {
            Yii::$app->response->format = Response::FORMAT_JSON;
        }
    }

this query work is good - search-city?q=query, but request search-country?q=query call execption "Undefined index: id". screen exception with response format as html http://storage6.static.itmages.ru/i/14/0701/h_1404203419_2466248_4caf027d5d.png
If i add 'id' to select i get "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous". Solve add 'net_city.id' and 'net_country.id' to select. Ended work actions:

 public function actionSearchCountry($q) {
        $this->ajaxOnly();
        return NetCountry::find()
            ->select(['country' => 'name_ru', 'net_country.id'])
            ->where(['like', 'name_ru', $q])
            ->innerJoinWith('clinics')
            ->asArray()->all();
    }

    public function actionSearchCity($q) {
        $this->ajaxOnly();
        return NetCity::find()
            ->select(['city' => 'name_ru', 'net_city.id'])
            ->where(['like', 'name_ru', $q])
            ->innerJoinWith('clinics')
            ->asArray()->all();
    }

[lynicidn]

relations in NetCountry:

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getClinics()
    {
        return $this->hasMany(Clinics::className(), ['country' => 'id']);
    }

NetCity:

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getClinics()
    {
        return $this->hasMany(Clinics::className(), ['city' => 'id']);
    }

[qiangxue]

When you call select() for AR queries, you have to explicitly include PK and relevant FKs. Yii won't do this for you automatically.

[lynicidn]

1 query do it :) different logic for analog queries. And i do write alias for pk and fk?

[qiangxue]

Yes, you have to prefix columns to disambiguate them.

[lynicidn]

thx for description.