Sort and filter data in Yii2 GridView, where the column is not in the database

Question

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

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

But it cannot handle sorting, because this 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, ]); 

Updated (Asc and Desc works, but not with filters)

 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; } 

Answer 1

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

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

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

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

STEP 3: edit the search method () to enable the calculated priority field

 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 ], ] ]); ... 

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

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

Answer 2

Removed

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

has changed

  'priority' => [ 'asc' => ['(probability * influence)' => SORT_ASC], 'desc' => ['(probability * influence)' => SORT_DESC], 'label' => 'Priority', ], 

and after $ this-> load ($ params);

  $query->andFilterWhere(['=', '(probability * influence)', $this->priority]); 

And the search works as needed! :)

Thanks for the help!