如何将Gridview的多列的SQL查询连接到一列进行搜索和过滤？

Let's say i have 5 columns merged to one in gridview,i would like to concatenate the column Filter function something like this

假设我有5列合并到gridview中的一列，我想连接列Filter函数这样的东西

SELECT strcat(Column1, Column2, Column3, Column4, Column5) as MainColumnName.

and when i search i have to perform some query like this,

当我搜索我必须执行这样的查询，

WHERE MainColumnName LIKE '%userinput%';

Question: How to implement it in the yii2,the result of the query will be reflected in dropdown filter in gridview.

问题：如何在yii2中实现它，查询的结果将反映在gridview的下拉过滤器中。

Thanks in Advance,

提前致谢，

1 个解决方案

#1

The simplest way is extend the functionalities of the search function so you can use the result in dataProvider

最简单的方法是扩展搜索功能的功能，以便您可以在dataProvider中使用结果

For this you should

为此你应该

setup your model properly for get the concatenad result eg using a getter

正确设置模型以获得concatenad结果，例如使用getter

  /* Getter for  full name */
  public function getMainName() {
      return $this->column1 . ' ' . $this->column2 . ' ' . $this->column3 . ' ' . $this->column4 . ' ' . $this->column5;
  }

  /* Your model attribute labels */
  public function attributeLabels() {
      return [
          /* Your other attribute labels */
          'mainName' => Yii::t('app', 'Main Column  Name')
      ];
  }

Setup your search model for filter and sort

设置搜索模型以进行过滤和排序

  /* your calculated attribute */
  public $mainName;

  /* setup rules */
  public function rules() {
     return [
      /* your other rules */
      [['mainName'], 'safe']
     ];
  }

  /**
   * setup search function for filtering and sorting 
   * based on mainName field
   */
  public function search($params) {
      $query = YourModel::find();
      $dataProvider = new ActiveDataProvider([
          'query' => $query,
      ]);

      /**
       * Setup your sorting attributes
       * Note: This is setup before the $this->load($params) 
       * statement below
       */
      $dataProvider->setSort([
          'attributes' => [
              'id',
              'mainName' => [
                  'asc' => ['column1' => SORT_ASC, 'column2' => SORT_ASC, 'column3' => SORT_ASC, 'column4' => SORT_ASC,  'column5' => SORT_ASC],
                  'desc' => ['column1' => SORT_DESC, 'column2' => SORT_DESC, 'column3' => SORT_DESC, 'column4' => SORT_DESC,  'column5' => SORT_DESC],
                  'label' => 'Full Name',
                  'default' => SORT_ASC
              ],
              'other_your_column'
          ]
      ]);

      if (!($this->load($params) && $this->validate())) {
          return $dataProvider;
      }

      ........

      /* Setup your custom filtering criteria */

      // filter by person full name

      $query->andWhere(" concat(column1, ' ', column2, ' ',column3, 
             ' ',column4, ' ',column5) 
                   LIKE concat('%','" . $this-mainName . "',  '%') ");

      return $dataProvider;
  }

And in your gridView

并在您的gridView中

  echo GridView::widget([
      'dataProvider' => $dataProvider,
      'filterModel' => $searchModel,
      'columns' => [
          ['class' => 'yii\grid\SerialColumn'],
          'id',
          'mainName',
          ['class' => 'yii\grid\ActionColumn'],
      ]
  ]);

You can find some sample in this http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/

你可以在这里找到一些样品http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/

#1