CODEWITHSUNDEEP
phpmysqlyii2
rmpstmp
rmpstmp

Reputation: 281

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?

Upvotes: 0

Views: 404

Answers (3)

Midhun
Midhun

Reputation: 3988

Since you have the relations in the model yii automatically generated the joined values . Try this code 

$apartments = Apartments::find()->all();
foreach($apartments as $apartment)
{
   echo $apartment->city->value;
}

sorry if this is not what you meant!

Upvotes: 1

Fabrizio Caldarelli
Fabrizio Caldarelli

Reputation: 3008

In joinWith, you missed join 'on' fields, apartments.cityId and city.id

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

Upvotes: 0

darkod
darkod

Reputation: 21

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();

Upvotes: 2

Related Questions