Yii2 sort by with calculations in an ActiveDataProviderRecord

Question

I would like to add a groupby with cacluations in yii2 ActiveDataProvider. SO currently i have the following fields in my database

tbl_products
   id, products, pts_log,pts_chum

SO i would like to group my data with the formula

pts_log * pts_chum / 100

So i have the following in my controller

ActiveDataProvider([
        'query' => $query,
        'sort' => ['defaultOrder' => ['(pts_log * pts_chum / 100)' => SORT_DESC]],
        'pagination' => [
            'pageSize' => $this->paginator['perPage']/2,
            'page' => $this->paginator['page']
        ],
    ]);

But am now getting an error

undefined (pts_log * pts_chum / 100)

This works with one item key like pts_log. What do i add to make sorting work with a formulae.

score 2 · Answer 1 · answered Oct 24 '19 at 13:28

You have to add an alias to the field the query is to be grouped by and use the alias in sort.

$query = (new \yii\db\Query())
    ->select ('count(*) as c, ((pts_log * pts_chum / 100) as calc_field')
    ->from('tbl_products')
    ->groupBy('calc_field');
$dataProvider = new \yii\data\ActiveDataProvider([
    'query' => $query,
    'sort' => ['defaultOrder' => ['calc_field' => SORT_DESC]],
]);

Use simple the desired aggregates instead of count(*).

Sergio Codev · Answer 2 · 2019-10-24T12:07:30.403

In this case You getting an error undefined (pts_log * pts_chum / 100) because it is not column name and not valid expression.

You need create new variable in ActiveRecord model and and fill it with data from the calculated expression.

Then this variable name use in sort 'sort' => ['defaultOrder' => ['expr_item' => SORT_DESC]] Look to http://webtips.krajee.com/filter-sort-summary-data-gridview-yii-2-0/ A similar option is considered here.

Or prepare $query like:

$tn = TblProdukts::tableName();
$query = TblProdukts::find()->select([
                        TblProdukts::tableName().'.*',
                        "(".$tn.".id*".$tn.".rank/100) AS expr"
                    ]);

In Active Record class create variable $expr;

If needed add it to safe rules:

public function rules()
    {
        return [
            [[  
                'expr'// expression
            ], 
                'safe'],
        ];
    }

Yii2 sort by with calculations in an ActiveDataProviderRecord

2 Answers2