How to sort custom field with ActiveDataProvider?

Question

Yii

Good afternoon. There is a Profile table with related policies

public function getPolicy() {
        return $this->hasMany(Policy::class, ['user_id' => 'user_id'])
            ->where(['status' => 'payed']);
    }

The site has a curator-agent-operator system. It is necessary, starting from the curator, to receive the sold policies of the entire network, display them in the GridView and sort by this field. It’s just that there are no problems with the output, but there are sorting problems, because. calculated field.
The search mechanism for all network policies is as follows:

public function getCount() {
        if (!empty(Yii::$app->request->get('period'))) {
            $period = Yii::$app->request->get('period');
            if (strpos($period, ' - ') !== false) {
                [$start_date, $end_date] = explode(' - ', $period);
                $start_date = date('Y.m.d', strtotime('midnight', strtotime($start_date)));
                $end_date = date('Y.m.d H:i:s', strtotime('tomorrow', strtotime($end_date)) - 1);
            }
        }

        $agents = User::getChildrenSQL($this->user_id);
        $usersChild = ArrayHelper::getColumn($agents, function ($element) {
            return (int)$element['id'];
        });
        $usersChild[] = $this->user_id;
        if (empty(Yii::$app->request->get('period'))) {
            $policies = Policy::find()->where(['IN', 'user_id', $usersChild])
                ->andWhere(['status' => 'payed'])->all();
        } else {
            $policies = Policy::find()->where(['IN', 'user_id', $usersChild])
                ->andWhere(['status' => 'payed'])->andWhere(['between', 'payed_date', $start_date, $end_date])->all();
        }
        return count($policies);
    }

In searchModel

public $count;
[ [ 'count' ], 'safe' ],
$dataProvider->setSort([
            'attributes'=>[
                'count' => [
                    'asc'=>['count'=>SORT_ASC,],
                    'desc'=>['count'=>SORT_DESC],
                    'default' => SORT_DESC
                ],
            ]
        ]);

The field can be displayed, but sorting, of course, is not. Went through many examples. Sort is obtained only when using ArrayDataProvider, but I would still prefer Active. Tell me in which direction to move? Thank you.

Answer 1

It seems to me that you should work better with an array and SQL queries for two reasons:
1. You will not spoil the essence with additional, unnecessary logic and connections.
2. Queries on simple arrays give data much faster than on AR objects.
If you still want to implement your idea, then go for the data denormalization approach. Create an additional counter field that you will change when creating and editing. Thus, the output and sorting of this field will not load the database, create a bunch of queries every time the sorting and filtering parameters change, since this will be a field in your table. On which you can also hang an index for acceleration.

Answer 2

You need to add a public property to the Profile model to display the number of policies sold.
At you it is now called $count.
There is also a connection with the Policy model.
So the request might look like this:

$query = Profile::find()
   /* получаем все поля из таблицы профиля и считаем записи в таблице полисов */
   ->select(['{{%profile}}.*', 'count' => 'COUNT({{%policy}}.id)'])
   /* подгружаем "не жадно" необходимые данные через связь getPolicy() */
  ->joinWith('policy', false)
 /* группируем записи по id профиля */
  ->groupBy('{{%profile}}.id')
/* сортируем по количеству проданных полисов в порядке убывания */
  ->orderBy(['count' => SORT_DESC]);

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

Answer 3

Use media queries + rem or css variables .