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"])
->joinWith("leasing")
->andFilterWhere([
'{{%payment}}.verified' => $params['PaymentSearch']['verified'] ?? [0, 1],
'{{%leasing}}.status' => [0, 1], // unpaid, paid
])->groupBy('leasing_id');
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.