ActiveDataFilter implementation in Yii 2.0, support for filtering parameters for fields that do not exist

1. In the topic selection list page, whether it has been extended, 0: no; 1: Yes, this field does not exist in the topic selection table. is calculated by program. $time is the current Beijing time, calculated by the server. as shown in Figure 1

Figure 1

$result['items'][$i]['is_deferred'] = ($item['status'] != Plan::STATUS_COMPLETED && $time > $item['ended_at']) ? 1 : 0 ;

2. In the latest version of the prototype, you need to support the filtering field: is_deferred. as shown in Figure 2

Figure 2

3. Through the rules of the analysis program calculation, the final analysis to obtain the query SQL to be generated. 1599189174 is the current Beijing time, which is calculated by the client.

// 已延期
((`pa_plan`.`status` != '6') AND (`pa_plan`.`ended_at` < '1599189174'))

// 未延期
((`pa_plan`.`status` = '6') OR (`pa_plan`.`ended_at` >= '1599189174'))

4. Added request parameters that have been extended: filter[and][0][and][0][status][neq]=6&filter[and][0][and][1][ended_at][lt]=1599189174. in line with expectations. as shown in Figure 3

Figure 3

SELECT * FROM `pa_plan` WHERE (`pa_plan`.`is_deleted`=0) AND (((`pa_plan`.`created_at` >= '1573716153') AND (`pa_plan`.`created_at` <= '1599119824')) AND (`pa_plan`.`importance`='3') AND (`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND ((`pa_plan`.`status` != '6') AND (`pa_plan`.`ended_at` < '1599189174'))) ORDER BY `pa_plan`.`id` DESC LIMIT 20

5. Unextended request parameter added: filter[and][0][or][0][status]=6&filter[and][0][or][1][ended_at][gte]=1599189174. in line with expectations. as shown in Figure 4

Figure 4

SELECT * FROM `pa_plan` WHERE (`pa_plan`.`is_deleted`=0) AND (((`pa_plan`.`created_at` >= '1573716153') AND (`pa_plan`.`created_at` <= '1599119824')) AND (`pa_plan`.`importance`='3') AND (`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND ((`pa_plan`.`status`='6') OR (`pa_plan`.`ended_at` >= '1599189174'))) ORDER BY `pa_plan`.`id` DESC LIMIT 20

6. Summary: In the ActiveDataFilter implementation in Yii 2.0, if the fields that do not exist need to be filtered, you need to find a way to convert them into the existing fields in the table. In order to use the bottom layer that comes with the framework. Avoid manual conversion of filter parameters.