Sorting STAT or calculated fields from the same table

Following the Wiki, I am trying to make the same, but the difference here is that I’m doing a count to the same model but with some conditionals.

I mean, I have a table called payment with the following fields:

  • id
  • leasing_id
  • amount
  • verified (0 or 1)
  • created_at

And I want to count the payments not verified, or to count the payments in a specific date, verified or not.
I can display the count in the gridview in a column called # payments but this column I can’t make it sorted.

In the search query, I have the subquery like:

$subQuery = Payment::find()->select(["{{%payment}}.leasing_id",  "COUNT({{%payment}}.id) as payment_number"])
                        '{{%payment}}.verified' => $params['PaymentSearch']['verified'] ?? [0, 1],
                        '{{%leasing}}.status' => [0, 1], // unpaid, paid

        if($params['Payment']['created_at']) {
            $subQuery = $subQuery
                ->andWhere([">=", "{{%payment}}.created_at", $params['PaymentSearch']['created_at']])
                ->andWhere(['<=', "{{%payment}}.created_at", strtotime("+1 day", $params['PaymentSearch']['created_at'])]);
        } else {
            $subQuery = $subQuery->andWhere(['<=', "{{%payment}}.created_at", strtotime(date('Y-m-d'))]);

$query->leftJoin(['totalPayments' => $subQuery], 'totalPayments.leasing_id = {{%payment}}.leasing_id');

And the sort options in the ActiveDataProvider:

'sort' => [
                'attributes' => [
                    'paymentCount' => [
                        'asc' => ['totalPayments.payment_number' => SORT_ASC],
                        'desc' => ['totalPayments.payment_number' => SORT_DESC],

I don’t need to filter anything, so I don’t use this column in the filter section, but I have no clue why this column can’t be sorted.

At first glance it looks good but do you have the $totalPayments attribute declared (public) in your search model? It is necessary for sorting by attribute which is not simple table model one.

Yes, as I said, I followed the wiki page to try to do it.