CODEWITHSUNDEEP
yii2
beginner
beginner

Reputation: 2032

How to connect 3 tables in yii2 and display in Gridview then make sorting work correctly

I have used the gii tool to create crud application. I have 3 tables the tbl_targetcities, lib_cities, and lib_provinces. I was able to connect lib_cities to tbl_targetciteis but not the lib_provinces. And also the sorting of city / Municipality does not work. It seems that it sorts according ti the ID.

tbl_target_cities

enter image description here

lib_cities

enter image description here

lib_provinces

enter image description here

sample View

enter image description here

So far here is my relation in the model.

public function getCityName()
{
  return $this->hasOne(LibCities::className(),['city_code'=>'city_code']);
}

in my view file...

    <?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        [
            'attribute'=>'city_code',
            'value'=>'cityName.city_name'
        ],
                   [
            'attribute'=>'prov code',
            'value'=>'cityName.city_name'
        ],
        'kc_classification',
        'cluster',
        'grouping',
         'priority',
        'launch_year',

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>

How to display the prov_name from lib_provinces???

EDIT to answer user2839376 question in the comment box

IN THE SEARCH MODEL CLASS 

$query = TblSpBub::find();
    $query->joinWith('brgyCode')->joinWith(['cityCode'])->joinWith(['cityCode.provCode']);

    $covered=  LibAreas::find()->where(['user_id'=>yii::$app->user->identity->id])->all();

    $query->all();

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort'=> ['defaultOrder' => ['id'=>SORT_DESC]],
    ]);

    $dataProvider->sort->attributes['city'] = [
    'asc' => ['lib_Cities.city_name' => SORT_ASC],
    'desc' => ['lib_Cities.city_name' => SORT_DESC],
    ];

    $dataProvider->sort->attributes['province'] = [
    'asc' => ['lib_provinces.prov_name' => SORT_ASC],
    'desc' => ['lib_provinces.prov_name' => SORT_DESC],
    ];

Upvotes: 2

Views: 3052

Answers (3)

Richard perris
Richard perris

Reputation: 511

Done it, Heres how.

in addition to the above code (original post)

// in Model I added an additional function

public function getTaskowner()
{
   return $this->hasOne(Tasks::className(), ['id' => 'task_id'])
       ->with(
           ['location','taskowner']
       );
}

and in view i did this

 ....
'columns' => [
 ....
[
  'class' => 'kartik\grid\DataColumn',
  'value'=> 'tasks.location.taskowner.name',
  .....
],
.....

and it worked

key points. used an array with the 'with->(..)' to include both then in the view added 'tasks.location.taskowner.name', to join them all

Upvotes: 0

vitalik_74
vitalik_74

Reputation: 4611

In LibCities model add new relation:

public function getProvince()
{
  return $this->hasOne(LibProvince::className(),['prov_code'=>'prov_code']);
}

And change getCityName relation. You should add with() for relation:

public function getCityName()
{
  return $this->hasOne(LibCities::className(),['city_code'=>'city_code'])->with(['province']);
}

And in view correct your columnto this:

 [
            'attribute'=>'prov code',
            'value'=>'cityName.province.prov_name'
        ],

Upvotes: 7

LolWalid
LolWalid

Reputation: 545

You have to use the function relations() in models.

In tbl_target_cities model:

public function relations()
  {
    return array(
      'city' => array(self::HAS_ONE, 'LibCities', 'city_code'),
    );
  }

In LibCities model :

public function relations()
  {
    return array(
      'province' => array(self::HAS_ONE, 'LibProvinces', 'prov_code'),
      'targets' => array(self::HAS_MANY, 'TargetCity', 'city_code',
    );
  }

This will allowed you to jump throw the LibCities model, now you can simply acces to prov name like this : 

$model->city->province->prov name;

Note : You need to have the 3 models defined.

EDIT

array(
  'name' => 'province name',
  'value' => $data->city->province->prov_name;
),

Upvotes: 0

Related Questions