在GridView Yii2中排序和过滤数据，其中列不在数据库中 [英] Sort and filter data in GridView Yii2 where column is not in database

问题描述

如果我有db中的2个字段 - 概率和影响力，并且我需要GridView中的这两个字段相乘的列。我设法在这里添加它：

If I have 2 fields in db - probability and influence and I need a column in GridView where these two fields are multiplied. I managed to add it there like:

    [
            'attribute' => 'priority',
            'format' => 'raw',
            'value' => function ($model) {
                return $model->influence * $model->probability;
            },
        ],

但无法处理排序，因为该列不在数据库中，向$查询添加过滤器只会导致错误。

But can't handle the sorting, because that column is not in db and adding filters to $query causes only errors.

    $query = Risks::find();
    $query->select(`probability*influence AS priority`);
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

已更新（适用于Asc和Desc，但不适用于过滤器）

public function search($params)
{
    $query = Risks::find();

    $query->joinWith(['author', 'proj']);

    $query->select('*, (probability * influence) as priority');

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

    $dataProvider->setSort([
        'attributes' => [
           // 'id',
            'probability',
            'risks',
            'influence',
            'del' => [
                'asc' => ['risks.del' => SORT_ASC],
                'desc' => ['risks.del' => SORT_DESC],
            ],
            'priority' => [
                'asc' => ['priority' => SORT_ASC],
                'desc' => ['priority' => SORT_DESC],
                'label' => 'Priority',
            ],
            'proj' => [
                'asc' => ['projects.name' => SORT_ASC],
                'desc' => ['projects.name' => SORT_DESC],
            ],
            'author' => [
                'asc' => ['users.name' => SORT_ASC],
                'desc' => ['users.name' => SORT_DESC],
            ]
        ]
    ]);

    $this->load($params);

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


    $query->andFilterWhere([
        'id' => $this->id,
        'proj_id' => $this->proj_id,
        'author_id' => $this->author_id,
        'influence' => $this->influence,
        'probability' => $this->probability,
        //'del' => $this->del,
    ])
        ->andFilterWhere(['like', 'projects.name', $this->proj])
        ->andFilterWhere(['like', 'users.name', $this->author]);

    $query->andFilterWhere(['like', 'risks', $this->risks]);

    $query->having('priority = '. $this->priority);
    //$query->having(['priority' => $this->priority]);

    return $dataProvider;
}

推荐答案

为您的基础风险模型添加getter函数：

STEP 1: Add a getter function to your base Risks model:

public function getPriority() {
    return ($this->probability * $this->influence);
}

步骤2：添加一个属性 priority 到您的模型 RisksSearch 并配置您的规则。


STEP 2: Add an attribute priority to your model RisksSearch and configure your rules.

/* your calculated attribute */
public $priority;

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

步骤3：编辑search（）方法，字段优先级
$ b

STEP 3: Edit the search() method to include the calculated field priority

public function search($params) {

    $query = Person::find();

    $query->select('*, (probability * influence) as priority');

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

    /**
     * Setup your sorting attributes
     * Note: This is setup before $this->load($params)
     */
    $dataProvider->setSort([
        'attributes' => [
            'id',
            'priority' => [
                'asc' => ['priority' => SORT_ASC],
                'desc' => ['priority' => SORT_DESC],
                'label' => 'Priority',
                'default' => SORT_ASC
            ],
        ]
    ]);
    ...

步骤4：添加 $在 $ this-> load（$ params）之后的查询 - >和FilterWhere（），以便能够过滤计算的字段

STEP 4: Add $query->andFilterWhere() after $this->load($params) to be able to filter the calculated field

// use the operator you wish, i.e. '=', '>', '<' etc
$query->andFilterWhere(['=', '(probability * influence)', $this->priority]);

这篇关于在GridView Yii2中排序和过滤数据，其中列不在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！