Hi to all,
i’m an Yii2 newbie and finally, after many attempts and forums readings , i need to be helped!
I have 2 mysql table like these below:
table A
ID | PINGED_HOSTNAME | PINGED_DOMAIN |…
1 | hn_abcdefy | .domain1 |…
2 | hn_abcdefg | .domain2 |…
3 | hn_abcdefh | .domain1 |…
table B
ID | CMDB_HOSTNAME | CMDB_ DOMAIN |…
1 | hn_abcdefy | .domain1 |…
2 | hn_abcdefx | .domain3 |…
3 | hn_abcdefh | .domain1 |…
The table A is filled by script in a scheduled task (in crontab). The table B is filled by direct and automatic CSV import.
For table A and table B i’ve generated with gii crud generator the views. But, in the search model of table B, i’ve added a virtual attribute called “trovatoinpinged” because i need a new boolean column in the index gridview that is true only if the tableB.cmdb_hostname is found in tableA.pinged_hostname column.
So, following this "stackoverflow.com/questions/28578057/sort-and-filter-data-in-gridview-yii2-where-column-is-not-in-database post or “webtips.krajee.com/filter-sort-calculated-related-fields-gridview-yii-2-0/” i’ve:
Added a safe rule to my base tableB model:
...
public function rules()
{
return [
[['cmdb_hostname', 'cmdb_owner', 'cmdb_state'], 'required'],
[['cmdb_confirmed'], 'integer'],
[['cmdb_insert_datetime','trovatoinpinged'], 'safe'],
[['cmdb_hostname', 'cmdb_domain', 'cmdb_owner', 'cmdb_state'], 'string', 'max' => 50],
[['cmdb_hostname', 'cmdb_domain', 'cmdb_owner', 'cmdb_state'], 'unique', 'targetAttribute' => ['cmdb_hostname', 'cmdb_domain', 'cmdb_owner', 'cmdb_state'], 'message' => 'The combination of Cmdb Hostname, Cmdb Domain, Cmdb Owner and Cmdb State has already been taken.'],
];
}
...
Added a getter function to my base tableB model:
...
public function gettrovatoinpinged() {
return $righe_trovate = (new Query())->select(['id'])->from(Hostslistunix::tableName())->where(['hostname' => $this->cmdb_hostname])->one() > 0 ? true : false;
}
...
Added an attribute "trovatoinpinged" to my model tableBSearch and configured my rules:
...
public $trovatoinpinged;
/**
* @inheritdoc
*/
public function rules()
{
return [
[['cmdb_id', 'cmdb_confirmed'], 'integer'],
[['cmdb_hostname', 'cmdb_domain', 'cmdb_owner', 'cmdb_state', 'cmdb_insert_datetime','trovatoinpinged'], 'safe'],
];
}
...
Added the new virtual attribute in the gridview colums of the table B view index:
...
'columns' => [
['class' => 'yii\grid\SerialColumn'],
//'cmdb_id',
'cmdb_hostname',
[
'attribute'=>'cmdb_domain',
'filter'=>ArrayHelper::map(Exportcmdbunix::find()->asArray()->orderby('cmdb_domain')->all(), 'cmdb_domain', 'cmdb_domain'),
],
//'cmdb_owner',
[
'attribute'=>'cmdb_state',
'filter'=>ArrayHelper::map(Exportcmdbunix::find()->asArray()->orderby('cmdb_state')->all(), 'cmdb_state', 'cmdb_state'),
],
[
'attribute'=>'cmdb_confirmed',
'class' => '\kartik\grid\BooleanColumn',
'trueLabel' => 'YES',
'falseLabel' => 'NO',
'trueIcon' => '<span class="glyphicon glyphicon-ok text-success"></span>',
'falseIcon' => '<span class="glyphicon glyphicon-remove text-danger"></span>'
],
[
'attribute'=>'trovatoinpinged',
'class' => '\kartik\grid\BooleanColumn',
'trueLabel' => 'YES',
'falseLabel' => 'NO',
'trueIcon' => '<span class="glyphicon glyphicon-ok text-success"></span>',
'falseIcon' => '<span class="glyphicon glyphicon-remove text-danger"></span>'
],
[
'header' => '<center><i class="glyphicon glyphicon-cog"></i>',
'class' => 'yii\grid\ActionColumn',
'template' => '{view} {update}',
],
],
...
…and everything seems to work properly:
But when i try to use the filter above the new column nothing happens.
In fact, in Section 3 and 4 of the StackOverflow guide, it explains how to add the where condition in my tableB searchmodel. But in the linked guide, explain how to use a virtual attribute calculated and related on the same DB table.
How to do in my case? Where and how can I make the join between the two tables?
Thanks in advance and sorry for my bad english!