How do I add a filter on yii2 models

I have a query used to read data from a view. I am then applying a non related sql filter to this, to take out the models I don’t need and then returning an arrayDataProvider instance. The filter I am applying is the promotion period. However, when I pass query params to this search model, it doesn’t work. How do I solve this?

Here’s the code to select data from the view:

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;
}

Here’s the dump of the params from the URL:

[
'from_date' => ''
'to_date' => ''
'PromotedStaffSearch' => [
    'COL_NAME' => 'CENTRAL ADMINISTRATION'
    'DEPT_NAME' => ''
    'GRADE_DESCR' => ''
    'DESG_NAME' => ''
    'GENDER' => ''
    'TRIBE_NAME' => ''
    'HOME_DISTRICT' => ''
]

]

Answer

Like i understand, you trying to find all models between specified period if it is set.

Try this:

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();
}

Documentation for Data Providers