Sort and filter data in GridView Yii2 where column is not in database
STEP 1: Add a getter function to your base Risks
model:
public function getPriority() {
return ($this->probability * $this->influence);
}
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']
];
}
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
],
]
]);
...
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]);
Removed
$query->select('*, (probability * influence) as priority');
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 search works as needed! :)
Thanks for help!