Custom filter in Gridview (Regular or Overdue)

Question

I have a registration_date column where I store the date that the customer was registered in the company (example: 2015-06-12). You'll need to filter if the customer has a failed or overdue registration (if registration_date is longer than 365 days).

In the preview screen I can display this condition, but I need to display as a filter in Gridview with 2 options (Regular or Overdue).

[
             'attribute' => 'registration_date',
             'format' => 'raw',
             'value' => function ($model) {
                 return $model->getRegistration($model->registration_date);
             },
             'filter' => [Regular or Overdue], ????
             'contentOptions' => $contentOptions,
             'headerOptions'  => $headerOptions,
],

How do I make this check in model ClientSearch?

Answer 1

What I understand is that you want to add a filter on the column registration_date which is as a Date type column, which should have 2 options Regular and Overdue and should filter the records based on

• Regular

  The option should filter out all the records that have the registration_date difference smaller than or equal to 365 days.

• Overdue

  The option should filter out all the records that have the registration_date difference from the current date greater than 365 days.

Your gridview column will look like below

GridView Changes

If the above is correct then, first of all, add the following configuration in your gridview column configuration. we will provide an active-dropdown in the filter option and assign it a name registration_status.

Note: I have used select2 for the dropdown you can use the default if you do not want, and change the $searchModel to the exact name for the client search model that you are passing to the filterModel option in your gridview.

[

    'filter' => \kartik\widgets\Select2::widget ( [
        'data' => $searchModel->statusOptions(),
        'model' => $searchModel ,
        'attribute' => 'registration_status' ,
        'options' => [
            'placeholder' => 'Registration Status' ,
            'class' => 'form-control' ,
            'id' => 'registration_status' ,
        ] ,
        'theme' => \kartik\widgets\Select2::THEME_BOOTSTRAP ,
        'pluginOptions' => [
            'allowClear' => true ,
        ] ,
    ] ) ,
    'attribute' => 'created_at' ,
    'label' => Yii::t ( 'app' , 'Registration Date' ) ,
] ,

Without Select2

[
    'filter' => Html::activeDropDownList ( $searchModel , 'registration_status' , $searchModel->statusOptions () , [ 'prompt' => 'Select Registration status' ] ) ,
    'attribute' => 'created_at' ,
    'label' => Yii::t ( 'app' , 'Registration Date' ) ,
] ,

ClientSearch Changes

• Add the constants on top of your ClientSearch model.
  • const STATUS_OVERDUE = 'overdue';
  • const STATUS_REGULAR = 'regular';

• Add the function statusOptions() inside the ClientSearch model.

      public function statusOptions() {
          return [
              self::STATUS_OVERDUE => 'Overdue' ,
              self::STATUS_REGULAR => 'Regular'
          ];
      }
  

• Add a public property registration_status inside the search model.

  public $registration_status

• Add this field we created to the safe list inside the rules()

  function.

  function rules(){
      return [
          //other rules
          [ [.... , 'registration_status' ] , 'safe' ] ,
      ];
  }
  

• Then the next thing is to accomplish the SQL condition which would filter out the records, you can use MySQL functions, DATEDIFF() and CURDATE(), like DATEDIFF(CURDATE(),registration_date)>365.

  So add the following inside the search() function of the search model before the last line return $dataProvider.

  if ( $this->registration_status === self::STATUS_OVERDUE ) {
      $query->andFilterWhere ( [ '>' , 'DATEDIFF(NOW(),registration_date)' , 365 ] );
  } else if ( $this->registration_status === self::STATUS_REGULAR ) {
      $query->andFilterWhere ( [ '<=' , 'DATEDIFF(NOW(),registration_date)' , 365 ] );
  }
  

Now you can filter the records based on the selected option and if you clear out the options in the drop-down it will show all of the records.