如何在 yii2 模型上添加过滤器

Question

我有一个用于从视图读取数据的查询。然后，我对此应用一个不相关的 sql 过滤器，以取出我不需要的模型，然后返回一个 arrayDataProvider 实例。我应用的过滤器是促销期。但是，当我将查询参数传递给该搜索模型时，它不起作用。我该如何解决这个问题？以下是从视图中选择数据的代码：public function search($params, $promotion_period){    $query = StaffEmploymentListView::find()->alias('LIST')            ->joinWith(['employment EMPLOYMENT'])            ->where(['LIST.EMPLOYMENT_TYPE' => 'PROMOTION']);       $query->andFilterWhere(['like', 'DEPT_NAME', $this->DEPT_NAME])            ->andFilterWhere(['like', 'COL_NAME', $this->COL_NAME])            ->andFilterWhere(['like', 'GRADE_DESCR', $this->GRADE_DESCR])            ->andFilterWhere(['like', 'DESG_NAME', $this->DESG_NAME])            ->andFilterWhere(['like', 'TRIBE_NAME', $this->TRIBE_NAME])            ->andFilterWhere(['like', 'GENDER', $this->GENDER])            ->andFilterWhere(['like', 'HOME_DISTRICT', $this->HOME_DISTRICT]);    $allModels = [];    if($promotion_period !== null){        $to_date = strtotime($promotion_period['to_date']);        $from_date = strtotime($promotion_period['from_date']);        foreach ($query->all() as $model)        {            $effective_date = strtotime($model->employment->APPOINT_DATE);            if ($effective_date >= $from_date && $effective_date <= $to_date){                $allModels[] = $model;            }        }    }    else{        foreach ($query->all() as $model)        {            $allModels[] = $model;        }    }    $dataProvider = new ArrayDataProvider([        'allModels' => $allModels,        'pagination' => [            'pageSize' => 20,        ],        'sort' => false    ]);    $this->load($params);    if (!$this->validate()) {        return $dataProvider;    }        return $dataProvider;}以下是 URL 中的参数转储：['from_date' => '''to_date' => '''PromotedStaffSearch' => [    'COL_NAME' => 'CENTRAL ADMINISTRATION'    'DEPT_NAME' => ''    'GRADE_DESCR' => ''    'DESG_NAME' => ''    'GENDER' => ''    'TRIBE_NAME' => ''    'HOME_DISTRICT' => '']]

Answer 1

据我了解，您试图查找between指定时间段（如果已设置）的所有模型。

尝试这个：

public function search($params, $promotion_period)

{

    $query = StaffEmploymentListView::find()->alias('LIST')

            ->joinWith(['employment']) //Here should be your relation name from StaffEmploymentListView model 

            ->where(['LIST.EMPLOYMENT_TYPE' => 'PROMOTION']);

   

    

    $dataProvider = new ActiveDataProvider([

        'query' => $query,

        'pagination' => [

            'pageSize' => 20,

        ],

        'sort' => false

    ]);

    $this->load($params);

    if (!$this->validate()) {

        return $dataProvider;

    }

    $query->andFilterWhere(['like', 'DEPT_NAME', $this->DEPT_NAME])

            ->andFilterWhere(['like', 'COL_NAME', $this->COL_NAME])

            ->andFilterWhere(['like', 'GRADE_DESCR', $this->GRADE_DESCR])

            ->andFilterWhere(['like', 'DESG_NAME', $this->DESG_NAME])

            ->andFilterWhere(['like', 'TRIBE_NAME', $this->TRIBE_NAME])

            ->andFilterWhere(['like', 'GENDER', $this->GENDER])

            ->andFilterWhere(['like', 'HOME_DISTRICT', $this->HOME_DISTRICT]);

    //$allModels = [];

    if(is_array($promotion_period) && isset($promotion_period['to_date']) && isset($promotion_period['from_date'])){

        //also check if you $promotion_period params are not empty

        $to_date = strtotime($promotion_period['to_date']);

        $from_date = strtotime($promotion_period['from_date']);

        $query->andFilterWhere(['BETWEEN', 'EMPLOYMENT_TABLE_NAME.APPOINT_DATE', $from_date, $to_date]);

    }

    

    //Now you can get all your filtered models as array like this:

    return $dataProvider->getModels();

}