Yii2 Query数据提供器多条件筛选GridView数据异常问题求解

核心问题点 #

• admin角色的查询逻辑未配置对应过滤规则：当前代码仅对maker角色的两个GridView做了字段非空/为空的条件限制，admin角色下的dataProvider仅配置了排序，未添加三个字段全部非空的过滤条件，同时完全未初始化unProcessed数据源，这是导致不符合预期的核心原因。
• 潜在兼容问题：仅判断字段为NULL无法覆盖字段存储空字符串的场景，部分表单提交空值时会写入空字符串而非NULL，会导致部分空值记录被误判为已填写。

修复后完整代码 #

if(Yii::$app->user->can('admin')){
    $searchModel = new DeliverySearch();
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
    // 补全admin角色下三个字段全部非空的过滤条件
    $dataProvider->query->andWhere(['not', ['delivery_trackcode' => null]]);
    $dataProvider->query->andWhere(['not', ['delivery_carrier' => null]]);
    $dataProvider->query->andWhere(['not', ['delivery_expedition_date' => null]]);
    $dataProvider->query->orderBy(['delivery_expedition_date'=>SORT_DESC]);

    // 补全admin角色下未完成记录的数据源
    $unProcessed = $searchModel->search(Yii::$app->request->queryParams);
    $unProcessed->query->andWhere(['or',['delivery_trackcode'=> NULL],['delivery_carrier'=> NULL],['delivery_expedition_date'=> NULL]]);
    $unProcessed->query->orderBy(['created_at'=>SORT_DESC]);
}
elseif(Yii::$app->user->can('maker')){
    $maker = Maker::find()->where(['user_id'=>Yii::$app->user->identity->id])->one();
    $searchModel = new DeliverySearch();
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
    $dataProvider->query->andWhere(['maker_id'=>$maker->id]);
    $dataProvider->query->andWhere(['not', ['delivery_trackcode' => null]]);
    $dataProvider->query->andWhere(['not', ['delivery_carrier' => null]]);
    $dataProvider->query->andWhere(['not', ['delivery_expedition_date' => null]]);
    $dataProvider->query->orderBy(['delivery_expedition_date'=>SORT_DESC]);

    $unProcessed = $searchModel->search(Yii::$app->request->queryParams);
    $unProcessed->query->andWhere(['maker_id'=>$maker->id]);
    $unProcessed->query->andWhere(['or',['delivery_trackcode'=> NULL],['delivery_carrier'=> NULL],['delivery_expedition_date'=> NULL]]);
    $unProcessed->query->orderBy(['created_at'=>SORT_DESC]);
}

return $this->render('index', [
    'searchModel' => $searchModel,
    'dataProvider' => $dataProvider,
    'unProcessed' => $unProcessed,
]);

可选优化（兼容空字符串场景） #

如果业务中存在空值存储为空字符串的情况，可以将空值判断条件调整为同时匹配NULL和空字符串：

// 非空条件调整示例
$dataProvider->query->andWhere(['and',
    ['not', ['delivery_trackcode' => null]],
    ['!=', 'delivery_trackcode', ''],
    ['not', ['delivery_carrier' => null]],
    ['!=', 'delivery_carrier', ''],
    ['not', ['delivery_expedition_date' => null]],
]);

// 为空条件调整示例
$unProcessed->query->andWhere(['or',
    ['delivery_trackcode'=> NULL],
    ['delivery_trackcode'=> ''],
    ['delivery_carrier'=> NULL],
    ['delivery_carrier'=> ''],
    ['delivery_expedition_date'=> NULL],
]);

内容的提问来源于stack exchange，提问作者DavidP