Sorting calculated fields in Yii2 (Grid View)

Question

i am need to sort some fields (asc,desc) in GridView, but same fields are calculated. Look at code below: SearchModel:

class ObjectSearch extends Object {
use SearchModelTrait;

public function rules()
{
    return [
        ['id', 'integer', 'min' => 1],
    ];
}

public function search($params)
{
    $this->company_id = \Yii::$app->user->identity->companyId;
    $query = Object::find()->where(['company_id' => $this->company_id]);
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'pagination' => false,
    ]);
    $dataProvider->setSort([
        'attributes' => [
            'id',
            'name',
            'lastReportResult' => [
                'asc' => ['lastReportResult' =>SORT_ASC ],
                'desc' => ['lastReportResult' => SORT_DESC],
                'default' => SORT_ASC
            ],
            'reportPercentDiff'
        ]
    ]);

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

    $this->addCondition($query, 'id');

    return $dataProvider;
}

Methods in Object model:

public function getLastReportResult()
{
    $lastReport = $this->getLastReport();
    $message = 0;

    if (!empty($lastReport)) {
        $statistic = new ReportStatistic($lastReport);
        $message = $statistic->getPercent();
    }

    return $message;
}

/**
 * @return int
 */
public function getReportPercentDiff()
{
    $lastReport = $this->getLastReport();
    $message = 0;

    if (!empty($lastReport)) {
        $statistic = $lastReport->getReportDiff();

        if (!empty($statistic['diff'])) {
            $message = $statistic['diff']['right_answers_percent_diff'];
        } elseif (!empty($statistic['message'])) {
            $message = $statistic['message'];
        }
    }
    return $message;
}

So, by this methods, i am calculating a values of two fields, which are need's sorting. This way doesn't working, i have a Database Exception, because object table hasn't this fields. exception How to do sorting of this fields ?

Answer 1

Update: I am the author of this answer and this answer is not accurate. Preferred way is to use database view

Add two public properties to ObjectSearch.php and mark it as safe

class ObjectSearch extends Object {
    use SearchModelTrait;
    public $lastReportResult, $reportPercentDiff;
    public function rules()
    {
        return [
            ['id', 'integer', 'min' => 1],
            [['lastReportResult', 'reportPercentDiff'], 'safe']
        ];
    }

    public function search($params)
    {
        $this->company_id = \Yii::$app->user->identity->companyId;
        $query = Object::find()->where(['company_id' => $this->company_id]);
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => false,
        ]);
        $dataProvider->setSort([
            'attributes' => [
                'id',
                'name',
                'lastReportResult' => [
                    'asc' => ['lastReportResult' =>SORT_ASC ],
                    'desc' => ['lastReportResult' => SORT_DESC],
                    'default' => SORT_ASC
                ],
                'reportPercentDiff' => [
                    'asc' => ['reportPercentDiff' =>SORT_ASC ],
                    'desc' => ['reportPercentDiff' => SORT_DESC],
                    'default' => SORT_ASC
                ],                
            ]
        ]);

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

        $this->addCondition($query, 'id');

        return $dataProvider;
}

Then in index.php (view file in which you are having grid view) add lastReportResult and reportPercentDiff in array of all attributes (list of all attributes ob Object model)

...
<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],

        
        // your other attribute here
        'lastReportResult',
        'reportPercentDiff',

        ['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>
...

For more info you can visit Kartik's blog at Yii