Joining tables in Yii2

Question

i'm new here.

I have read a lot of answers about joining tables but cant seem to figure it out.

I want to join 2 tables i have sap_items_slo with a lot of columns: (i need PropertyCode and PropertyName):

id  |   name  |  etc..  | PropertyCode | PropertyName
-----------------------------------------------------
5   |Product1 |  etc..  |       1      | Fill
75  |Product2 |  etc..  |       2      | RTG
55  |Product3 |  etc..  |       2      | Implant

and other table propery_hr with columns:

PropertyCode | PropertyName  
-----------------------------
     1       |     Fill2      
     2       |     RTG2    
     3       |     Implant2

they look like this. I want to use it like this so it will translate propertyNames for some users. These tables are from different databases. I can call the first database and output it correctly, but for

userCountry = Yii::$app->user->identity->country;
if($userCountry == 'SomeCoutnry') {
     *join tables(display ProperyName for each sap_items_slo PropertyCode from property_hr)* 
  }

This is my code SapItemsSearch.php

    public function search($params)
{   

    $query = SapItems::find()->where(['deleted'=>'no', 'sap_items_slo.country'=>Yii::$app->params['work_country']]);
    $query->andWhere("ManufacturerCode !=86 and ManufacturerCode !=84 and ManufacturerCode !=83 and ManufacturerCode !=48");
    $query->with(['sapPrice','image']);


    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'pagination' => [
            'pageSize' => 1000
        ]
    ]);

    $this->load($params);

    if (!$this->validate()) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }

SapItemsController

    public function actionIndex()
{
    $searchModel = new SapItemsSearch();
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

    $js = "$('#imagemodal').on('shown.bs.modal', function (event) {
        var button = $(event.relatedTarget);
        var src = button.attr('rel');
        var modal = $(this)
        modal.find('.modal-body').html(\"<div style='text-align:center;'><img src='https://www.dental-medical.rs/upload/product/\"+src+\"' /></div>\");
    })";

    $this->getView()->registerJs($js);

    return $this->render('index', [
        'searchModel' => $searchModel,
        'dataProvider' => $dataProvider,
    ]);
}

I display it in index.php in gridColumns

        $gridColumns = [
        [
            'attribute' => 'PropertyCode',
            'label' => 'Property',
            'value' => 'PropertyName',
            'filter' => ArrayHelper::map(SapItems::find()->select('PropertyCode,PropertyName')->where('PropertyName<>""')->andWhere(['country'=>Yii::$app->params['work_country']])->distinct()->orderBy('PropertyName')->all(),'PropertyCode','PropertyName'),
            'filterType'=>GridView::FILTER_SELECT2,
            'filterWidgetOptions'=>[
                'pluginOptions'=>['allowClear'=>true],
            ],
            'filterInputOptions'=>['placeholder'=>'-- Property --'],
        ],

 //And then echo gridView
        echo GridView::widget([
        'rowOptions'=> function ($model, $key, $index, $grid) {
            if($model->OnHand > 0)
                return ['class' => 'success'];
            else
                return ['class' => 'danger'];
        },
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => $gridColumns,
        'pjax' => false,
        'layout' => '<div style="margin-bottom:10px;"><h2 style="font-size:25px;" class="panel-title pull-left"><i class="glyphicon glyphicon-user"></i> '.$this->title.'</h2><div class="pull-right">{toolbar}</div><div class="clearfix"></div></div>{items}<div class="pull-left" style="margin-top:-20px;">{pager}</div><div class="pull-right">{summary}</div><div class="clearfix"></div>',

I hope this is enough information, i am given this project in Yii2 and im not sure how to do this. I have tried in multiple ways but without luck. I appreciate any help! if you want me to post any other information fill free to ask.

Answer 1

For joining two table adn use in gridview with filter you need these sted

in your SapItems Model you need a public function for the relation

I assume the propery_hr table have a model class named Property

public function getProperty()
{
    return $this->hasOne(Property::className(),
     ['PropertyCode' =>   'PropertyCode']);
}

Then you can build a getter for PropertyName

public function getPropertyName() {
    return $this->property->PropertyName;
}

then in your gridview you can use the PropertyName

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'summaryOptions' => ['class' =>'dfenx_pagination_summary',],
    'pager' => ['options' => ['class'=> 'pagination pull-right']],
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        .....
        'PropertyName', 
        .....      

for search and filter you must add in your seachModel a public var for filter ..

public $PropertyName;

And define this var safe for search

public function rules()
{
    return [
        ... 
        [[  'PropertyName', ], 'safe'],

    ];
}

in SapItemsSearch you must add a joinwith you relation

        if (!($this->load($params) && $this->validate())) {
        $query->joinWith(['Property']);

        return $dataProvider;
    }

at this point you can retrieve the data in you modelSearch with

->andFilterWhere(['like','PropertyName', $this->PropertyName])

You can find an useful tutorial to yuor problem in this doc http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/