rmpstmp rmpstmp - 7 months ago 38
PHP Question

joinWith return null foreign key value

Need to select some field from 2 tables using

joinWith()
method. I have 2 tables: apartments and city.

Apartments table has cityId field and city table has 2 fields: id and value.

Something like this:

enter image description here

I want to select some fields from apartments table and via cityId field get name (value) of city.

Relations in apartments model:

public function getCity() {
return $this->hasOne(City::className(), ['id' => 'cityId']);
}


Relations in city model:

public function getApartments() {
return $this->hasMany(Apartments::className(), ['cityId' => 'id']);
}


In controller I build query using
joinWith()
method, but it not return city.value field.

$apartments = Apartments::find()->select('apartments.title, city.value')->joinWith('city')->all();


print_r($apartments)
returns this:

Array ( [0] => app\models\Apartments Object ( [file] => [_attributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 1 ) [_oldAttributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 1 ) [_related:yii\db\BaseActiveRecord:private] => Array ( [city] => ) [_errors:yii\base\Model:private] => [_validators:yii\base\Model:private] => [_scenario:yii\base\Model:private] => default [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) ) [1] => app\models\Apartments Object ( [file] => [_attributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 2 ) [_oldAttributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 2 ) [_related:yii\db\BaseActiveRecord:private] => Array ( [city] => ) [_errors:yii\base\Model:private] => [_validators:yii\base\Model:private] => [_scenario:yii\base\Model:private] => default [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) ) [2] => app\models\Apartments Object ( [file] => [_attributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 3 ) [_oldAttributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 3 ) [_related:yii\db\BaseActiveRecord:private] => Array ( [city] => ) [_errors:yii\base\Model:private] => [_validators:yii\base\Model:private] => [_scenario:yii\base\Model:private] => default [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) ) [3] => app\models\Apartments Object ( [file] => [_attributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 4 ) [_oldAttributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 4 ) [_related:yii\db\BaseActiveRecord:private] => Array ( [city] => ) [_errors:yii\base\Model:private] => [_validators:yii\base\Model:private] => [_scenario:yii\base\Model:private] => default [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) ) [4] => app\models\Apartments Object ( [file] => [_attributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 5 ) [_oldAttributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 5 ) [_related:yii\db\BaseActiveRecord:private] => Array ( [city] => ) [_errors:yii\base\Model:private] => [_validators:yii\base\Model:private] => [_scenario:yii\base\Model:private] => default [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) ) [5] => app\models\Apartments Object ( [file] => [_attributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 6 ) [_oldAttributes:yii\db\BaseActiveRecord:private] => Array ( [title] => Квартира 6 ) [_related:yii\db\BaseActiveRecord:private] => Array ( [city] => ) [_errors:yii\base\Model:private] => [_validators:yii\base\Model:private] => [_scenario:yii\base\Model:private] => default [_events:yii\base\Component:private] => Array ( ) [_behaviors:yii\base\Component:private] => Array ( ) ) )


I don't see city.value field in this response, but see city field without any value.

Can you help me? What is problem?

Answer

Try this:

$query = new Query;
$query->select(['apartments.title as app_title', 'city.value as city_val'])  
              ->from('apartments')
              ->join('INNER JOIN', 
                  'city',
                  'city.id =apartments.cityId'
                  ); 

    $command = $query->createCommand();
    $data = $command->queryAll();