如何连接三个表并在网格视图中获取值 [英] How to join three tables and get value in grid view

问题描述

我有三张桌子:

1. contacts 有许多组
2. contact_groups 有很多联系人
3. contact_contact_groups

表格中的列联系方式

contact_id |联系人姓名

表中的列contact_groups

group_id |group_name

表中的列contact_contact_groups

contact_contact_group_id |contact_id |group_id

模型

联系人模型

公共函数 getContactContactGroups(){返回 $this->hasMany(ContactContactGroups::className(),['contact_id' =>'contact_id']);}

contact_groups 模型

公共函数 getContactContactGroups(){返回 $this->hasMany(ContactContactGroups::className(),['group_id' =>'group_id']);}

contact_contact_groups 模型

公共函数getGroup(){返回 $this->hasOne(ContactGroups::className(), ['group_id' => 'group_id']);}公共函数 getContact(){return $this->hasOne(Contacts::className(), ['contact_id' => 'contact_id']);}

我想像这样显示网格:

-----------------------------联系人姓名 |组的名字-----------------------------我 |未分类妈妈 |家庭约翰 |商业

查看

$数据提供者，'过滤器模型' =>$搜索模型，'tableOptions' =>['class' =>'table table-striped table-bordered'],'列' =>[['属性' =>'联系人姓名'，'价值' =>'联系人姓名'，],['属性' =>'contactContactGroups.group_id','价值' =>'contactContactGroups.group.group_name','过滤器' =>Html::activeDropDownList($searchModel, 'group_id', ArrayHelper::map(ContactGroups::find()->where(['group_status'=>'ACTIVE'])->asArray()->all(), 'group_id', 'group_name'),['class'=>'form-control','prompt' => '选择组']),],],]);?>

ContactsController

公共函数 actionIndex() {$this->unsetThisButton(array(4,5));$searchModel = new ContactsSearch();$dataProvider = $searchModel->search(Yii::$app->request->queryParams);返回 $this->render('index', ['搜索模型' =>$搜索模型，'数据提供者' =>$数据提供者，]);}

但它显示 (not set) 不是 group_name .

解决方案

我发现了这样的简单东西 :)

网格(视图)

<预><代码>['属性' =>'contactContactGroups.group_id','值'=>函数($数据){$d = 数组();foreach ($data->contactContactGroups as $k=>$m){$d[] = ContactContactGroups::get_group_name_by_id($m->group_id);}返回内爆($d, ', ');},'过滤器' =>Html::activeDropDownList($searchModel, 'group_id', ArrayHelper::map(ContactGroups::find()->where(['group_status'=>'ACTIVE'])->asArray()->all(), 'group_id', 'group_name'),['class'=>'form-control','prompt' => '选择组']),],

models/ContactContactGroups.php 模型

我创建函数get_group_name_by_id($id)

公共静态函数get_group_name_by_id($id){$model = ContactGroups::find()->where(["group_id" => $id])->one();如果(！空($模型)){返回 $model->group_name;}返回空；}

所以结果是:

联系方式 |类别-------------------------------我 |商务、家庭埃里克 |商务、办公约翰 |办公室

谢谢@scaisEdge，你给了我一些线索 ;)

I have three tables :

1. contacts hasMany groups
2. contact_groups hasMany contacts
3. contact_contact_groups

columns in table contact

contact_id | contact_name

columns in table contact_groups

group_id | group_name

columns in table contact_contact_groups

contact_contact_group_id | contact_id | group_id

MODEL

contacs model

public function getContactContactGroups()
{
    return $this->hasMany(ContactContactGroups::className(),
                          ['contact_id' => 'contact_id']);
}

contact_groups model

public function getContactContactGroups()
{
    return $this->hasMany(ContactContactGroups::className(),
                          ['group_id' => 'group_id']);
}

contact_contact_groups model

public function getGroup()
{
  return $this->hasOne(ContactGroups::className(), ['group_id' => 'group_id']);
}

public function getContact()
{
  return $this->hasOne(Contacts::className(), ['contact_id' => 'contact_id']);
}

I want to display grid like this :

-----------------------------
Contact Name | Group Name
-----------------------------
Me           | Uncategorized
Mother       | Family
Jhon         | Business

VIEW

<?= GridView::widget([
  'dataProvider' => $dataProvider,
  'filterModel' => $searchModel,
  'tableOptions' =>['class' => 'table table-striped table-bordered'],
  'columns' => [
    [
      'attribute' => 'contact_name',
      'value' => 'contact_name',
    ],
    [
      'attribute' => 'contactContactGroups.group_id',
      'value' => 'contactContactGroups.group.group_name',
      'filter' => Html::activeDropDownList($searchModel, 'group_id', ArrayHelper::map(ContactGroups::find()->where(['group_status'=>'ACTIVE'])->asArray()->all(), 'group_id', 'group_name'),['class'=>'form-control','prompt' => 'Select Group']),
    ],
  ],]); 
?>

ContactsController

public function actionIndex() { 
    $this->unsetThisButton(array(4,5));   
    $searchModel = new ContactsSearch(); 
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams); 
    return $this->render('index', [ 
          'searchModel' => $searchModel, 
          'dataProvider' => $dataProvider,
     ]); 
}

But it showing (not set) not a group_name .

解决方案

I Found simple stuff like this :)

GRID (VIEW)

[
        'attribute' => 'contactContactGroups.group_id',
        'value'=>function ($data) {
            $d = array();
            foreach ($data->contactContactGroups as $k=>$m)
            {
              $d[] = ContactContactGroups::get_group_name_by_id($m->group_id);
            }
            return implode($d, ', '); 
          },
        'filter' => Html::activeDropDownList($searchModel, 'group_id', ArrayHelper::map(ContactGroups::find()->where(['group_status'=>'ACTIVE'])->asArray()->all(), 'group_id', 'group_name'),['class'=>'form-control','prompt' => 'Select Group']),
      ],

models/ContactContactGroups.php Model

I create function get_group_name_by_id($id)

public static function get_group_name_by_id($id){
  $model = ContactGroups::find()->where(["group_id" => $id])->one();
  if(!empty($model)){
    return $model->group_name;
  }
  return null;
}

so the result is :

Contact   | Category
-------------------------------
Me        | Business, Family
Erick     | Business, Office
Jhon      | Office

Thank's @scaisEdge, you give me some clue ;)

这篇关于如何连接三个表并在网格视图中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！