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 ?

Hello. Have you find the answer for this? – BoJack Horseman May 15 '17 at 00:43 — BoJack Horseman, May 15 '17 at 00:43

Sohel Ahmed Mesaniya · Accepted Answer · 2020-12-29T16:00:09.420

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

This answer just plainly doesn't work if you don't have columns `lastReportResult` and `reportPercentDiff` in the database table. — hijarian, Mar 10 '17 at 12:02
Well in that case if you don't have that columns then you can define a attribute in grid view in following way. RelatioName.columnName. Eg if post table having column userId is related to user having PK Column id and other column named name and if you have relationship as getUser In post model Then giving user.name as attribute in grid view will work fine. — Sohel Ahmed Mesaniya, Mar 11 '17 at 14:12
I mean the case when you *don't have this column at all*, nowhere in the database, it's 100% calculated on PHP side inside the Yii2 model based on the other data from database. You can set up filtering by such column and you can display it in grid just fine but to make sorting by it is a horrible pain and is being done in completely different way. — hijarian, Mar 11 '17 at 15:38
Ya but you can accomplish it. http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/ — Sohel Ahmed Mesaniya, Mar 11 '17 at 19:01
Looking into doing this years later here, i can attest this does not work, nor does this proposed solution from Kartik work for totally calculated fields. the accepted answer should be negated and this should be marked as not solved. — Andrelope, Mar 24 '20 at 20:13
I have edited the answer stating that my answer is not accurate. Database view (https://dev.mysql.com/doc/refman/8.0/en/create-view.html) is preferred in that case. Also I have a blog a [blog post](https://sohelahmed.site/blog/sorting-calculated-columns-in-yii-2) + [gist (schema+data)](https://gist.github.com/SOHELAHMED7/1a8429d57f9f264eb40b6cfce87439f6) demonstrating calculated field in a database table using view. — Sohel Ahmed Mesaniya, Dec 29 '20 at 16:02

Salleh · Answer 2 · 2020-07-30T02:17:13.680

Though this is an old thread, stumbled upon this and tried to find other method to achieve sorting of purely calculated field to no avail... and this post unfortunately is not an answer as well... It just that I feel the need to post it here to give a heads up to those that still looking for the solution so as not to scratch their heads when trying the solution given and still fail.

The given example from documentation or referred links as far as I have tested only works if you have a column within the database schema (whether in the main table or the related tables). It will not work if the virtual attribute/calculated field you create is based on calculating (as an example multiplication of 2 column on the table)

then, if we use a virtual attribute 'purchase_total' for model 'purchase' which is the multiplication of quantity and unit_price (from the join table of purchase and product on product_id), eventually you will hit an error saying 'purchase_total' column can not be found when you tried to sort them using the method discussed so far.

Calculating field is a good thing for fast development, but though the years, i think, if it can be calculated by your db - you should use db. — Max Maximov, Jul 30 '20 at 08:59
Arguments for this : thin model layer and float 0,3 problem. Try to calculate dynamic fields in db and use decimal type for prices (or use fixed point arithmetic approach). — Max Maximov, Jul 30 '20 at 10:06

Sorting calculated fields in Yii2 (Grid View)

2 Answers2