Yii2 sort by calculated related fields

Question

Over the last couple of hours, I'm trying to create a gridview sortable calculated column without success, I tried every guide out there, followed the exact same steps and got no ware.

The calculated relation:

public function getOrderAmount() {
    return $this->hasMany(Transaction::className(), ['user_id' => 'id'])->sum('sum');
}

The UserSearch Model:

class UserSearch extends User
{
    public $orderAmount;

    public function rules()
    {
        return [
            [['id'], 'integer'],
            [['orderAmount'], 'safe'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        return Model::scenarios();
    }

    public function search($params)
    {
        $query = User::find();

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);


        $dataProvider->setSort([
            'attributes'=>[
                'id',
                'name',
                'orderAmount'=>[
                    'asc'=>['orderSum.order_amount'=>SORT_ASC],
                    'desc'=>['orderSum.order_amount'=>SORT_DESC],
                    'label'=>'Order Name'
                ]
            ]
        ]);

        $this->load($params);

        if (!$this->validate()) {
            return $dataProvider;
        }

        $query->andFilterWhere(['orderSum.order_amount'=>$this->orderAmount]);

        return $dataProvider;
    }
}

The view:

<?= GridView::widget([
            'dataProvider' => $dataProvider,
            'filterModel' => $searchModel,
            'responsive' => true,
            'hover' => true,
            'perfectScrollbar' => true,
            'columns' => [
                        'orderAmount',
            ],
        ]); ?>

This is the guide I followed in the example above http://webtips.krajee.com/filter-sort-summary-data-gridview-yii-2-0/

I've got an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'orderSum.order_amount' in 'order clause'
The SQL being executed was: SELECT * FROM `tbl_users` WHERE `id` IN ('1', '56') ORDER BY `orderSum`.`order_amount` LIMIT 20

Note: I removed other code blocks for easier debugging.

Answer 1

 1054 Unknown column 'orderSum.order_amount' in 'order clause'

Error basically says it can't find column because even though you declared relation on model, you didn't use it yet.

In your UserSearch model you should use relation, before loading your parameters.

    $query->joinWith('orderAmount as oa');
    $this->load($params);

Also don't forget to add array_merge method on top your UserSearch model because you are using another attribute.

 public function attributes()
   {
     // add related fields to searchable attributes
     return array_merge(parent::attributes(), ['oa.order_amount ']);
   }

and lastly, since we rewrite relation name as "oa" you should change "orderAmount" to "oa"

         $dataProvider->setSort([
        'attributes'=>[
            'id',
            'name',
            'orderAmount'=>[
                'asc'=>['oa.order_amount'=>SORT_ASC],
                'desc'=>['oa.order_amount'=>SORT_DESC],
                'label'=>'Order Name'
            ]
        ]
    ]);