Sorting calculated fields in Yii2 (Grid View) getter param

Question

How get i sort my rows by field i get from model getter like this

 * Returns the score for this team
 */
public function getScore() {
    $score = 0;
    if (!empty($this->bonus_points)) {
        $score += $this->bonus_points;
    }
    foreach (Attempt::find()->where(['team_id' => $this->id, 'marked' => 1])->all() as $attempt) {
        $score -= $attempt->cost;
        $score += $attempt->reward;
    }
    return $score;
}

View Code

  GridView::widget([
    'id' => 'quickfire-grid',
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'filterPosition' => GridView::FILTER_POS_HEADER,
    'tableOptions' => [
        'class' => 'table'
    ],
    'layout' => "{summary}\n{items}\n{pager}",
    'columns' => [
        ['class' => 'yii\grid\CheckboxColumn'],
        'name',
        [
            'attribute' => 'bonus_points',
            'label' => 'Adjustment',
            'filter' => false
        ],
        [
            'attribute' => 'score',
            'label' => 'Score',
            'filter' => false,
        ],
        [
            'label' => 'Adjust score',
            'format' => 'raw',
            'value' => function ($data) use ($game) {
                return Html::input('text', 'score-' . $data->id, '0', ['id' => 'score-' . $data->id]) . ' ' . Html::a('Adjust score', '#', ['class' => 'btn btn-danger btn-adjust-score', 'data-team-id' => $data->id, 'data-href' => Url::toRoute(['adjust-scores', 'game_id' => $game->id, 'skipConfirmation' => true])]);
            },
        ],
    ],
]);

Is it possible to sort grid by score field ? I think need to add some javascript code. I have read this article but there is no solution https://www.yiiframework.com/wiki/621/filter-sort-by-calculatedrelated-fields-in-gridview-yii-2-0.

Yaroslav Hoisa Yaroslav Hoisa · Accepted Answer · 2019-09-26T14:13:54

Team::find()->select(['total_score' => 'ifnull(s.score, 0)+team.bonus_points'])
  ->leftJoin([
     's' => Attempt::find()
            ->select('team_id, SUM(reward-cost) as score')
            ->where(['marked' => 1])
            ->groupBy('team_id')
  ], 's.team_id=team.id')
  ->orderBy('total_score')

Something like this) Modify select with your needs...

Sorting calculated fields in Yii2 (Grid View) getter param

1 Answers