I have my model SPARGARGOMENTO - many -> SPPERPERCORSO - many -> SPDMNDOMANDA
For clarity these are the relation defined in SPARGARGOMENTO
public function getSPPRCPERCORSO() {
return $this->hasMany(SPPRCPERCORSO::className(), ['ARG_ID_ARGOMENTO' => 'ARG_ID_ARGOMENTO']);
}
public function getSPDMNDOMANDA() {
return $this->hasMany(SPDMNDOMANDA::className(), ['PRC_ID_PERCORSO' => 'PRC_ID_PERCORSO' ])
->via('sPPRCPERCORSO');
}
The relation is working fine and I need to query the related table as well in order to use some filters.
What I want to obtain is an array like the following (‘id’ and ‘name’ are alias column of the model SPARGARGOMENTO):
Array(
[0] => Array(
[id] => 151
[name] => Abitazione
)
[1] => Array(
[id] => 178
[name] => Esenzioni
)
[2] => Array(
[id] => 72
[name] => Agevolazioni
)
.....
So we go by steps, at first a simple query:
$out = \app\models\SPARGARGOMENTO::find()
->select(['id'=>'SP_ARG_ARGOMENTO.ARG_ID_ARGOMENTO', 'name'=>'ARG_DESC_ARGOMENTO'])
->asArray(false)
->all();
If I print it the result is something like:
[0] => app\models\SPARGARGOMENTO Object(
[id] => 79
[name] => AGEVOLAZIONI AUSILI E PROTESI
[_attributes:yii\db\BaseActiveRecord:private] => Array()
[_oldAttributes:yii\db\BaseActiveRecord:private] => Array()
[_related:yii\db\BaseActiveRecord:private] => Array(
[sPPRCPERCORSO] => Array()
[sPDMNDOMANDA] => Array()
)
.....
It seem exaclty what I want, perfet so lets try by ->asArray(true)
Array(
[0] => Array(
[id] => 151
[name] => Abitazione
)
[1] => Array(
[id] => 178
[name] => Esenzioni
)
[2] => Array(
[id] => 72
[name] => Agevolazioni
)
.....
Yes I got it!!!
Then I add ->joinWith(‘sPDMNDOMANDA’) so I can use the filters I need…
$out = \app\models\SPARGARGOMENTO::find()
->joinWith('sPDMNDOMANDA')
->select(['id'=>'SP_ARG_ARGOMENTO.ARG_ID_ARGOMENTO', 'name'=>'ARG_DESC_ARGOMENTO'])
->where(['ARG_FLAG_VALIDO' => 'S'])
->andWhere(['TMA_ID_TEMA' => $id])
->andWhere(['DMN_FLAG_ATTIVA'=>'S'])
->andWhere(['is not', 'SP_DMN_DOMANDA.PRC_ID_PERCORSO', null])
->andWhere(['TDM_ID_TIPO_DOMANDA'=>2])
->asArray(false)
->all();
If I print it the result is something like:
[0] => app\models\SPARGARGOMENTO Object(
[id] => 79
[name] => AGEVOLAZIONI AUSILI E PROTESI
[_attributes:yii\db\BaseActiveRecord:private] => Array()
[_oldAttributes:yii\db\BaseActiveRecord:private] => Array()
[_related:yii\db\BaseActiveRecord:private] => Array(
[sPPRCPERCORSO] => Array()
[sPDMNDOMANDA] => Array()
)
.....
Which is what I expect, the 2 alias column with sPPRCPERCORSO and sPDMNDOMANDA not populated as they have no column part of the select.
But I want it as an array so I set ->asArray(true) again and here is the problem:
Undefined index: ARG_ID_ARGOMENTO
- in /var/www/solidarietabo.loc/vendor/yiisoft/yii2/db/ActiveQuery.php at line 263
What I expect is that it works since I’m only asking the result in a different type.
And this is the first problem I found.
Second issue:
Facing this I tried to solve the problem by adding ARG_ID_ARGOMENTO to the select but specifing to use SP_ARG_ARGOMENTO.ARG_ID_ARGOMENTO (which is the same I use for the alias column ‘id’) so related ActiveRecord do not get populated.
....
->select(['SP_ARG_ARGOMENTO.ARG_ID_ARGOMENTO', 'id'=>'SP_ARG_ARGOMENTO.ARG_ID_ARGOMENTO', 'name'=>'ARG_DESC_ARGOMENTO'])
...
But this fecth also the SPPRCPERCORSO related record and I think it shouldn’t since the column (even if part of the relation) is the one of the main table.