How to create a view filter plugin to filter a column created by a view field plugin

Question

I have created a view field plugin to display an "internal status" for each node in a view.

Those status are not stored in a table (and should not) because each status may vary depending the current date (and some other fluctuating data).

The possible status outputted by this virtual field (not sure it is the correct terminology) are 1,2 or 3 (integer)

Here is the simplified code

1-Declare the virtual field in hook_views_data_alter()

function mymodule_views_data_alter(array &$data) {
  $data['node']['internal_status'] = [
    'title' => t('Internal Status'),
    'field' => [
      'title' => t('Internal Status'),
      'help' => t("Display internal status"),
      'id' => 'internal_status',
    ],
  ];
}

2- Create the view plugin

/**
 * Field handler to display internal status.
 *
 * @ViewsField("internal_status")
 */
class NodeInternalStatus extends FieldPluginBase {

  public function query() {}

  public function render(ResultRow $values) {
    $node = $values->_entity;
    $value= return_1_2_or_3_depending_on_dyn_data_like_the_current_date($node);
    switch ($value){
      case 1:
        return t('This is 1');
      case 2:
        return t('This is 2');
      case 3:
        return t('This is 3');
    }
    //will never go here if everything is ok
    return t('n/a');
  }
}

My Question: I would like to create a filter plugin to filter nodes according their internal status [by using return_1_2_or_3_depending_on_dyn_data_like_the_current_date($node)]

I have tried to extend the inOperator class but as the real column of the "internal status" field doesn't exists in the DB, I keep getting the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_field_data.internal_status' in 'where clause':

Answer 1

After some digging I will answer myself to this question: it is not possible to create a filter on a non existent DB column.

How to solve this issue:

In my example, the values of this non existent column are calculated via the function return_1_2_or_3_depending_on_dyn_data_like_the_current_date($node).

We couldn't use this function to create the filter. Instead, for each value returned by the function (in my case 1,2 or 3), we have to write a query with the same logic used in the "dropped" function.

All the logic must reside in the query method of the filter plugin.

Here is an example:

1- Add the filter in hook_views_data_alter

function mymodule_views_data_alter(array &$data) {
  $data['node_field_data']['internal_status_filter'] = [
    'title' => t('Filter by Internal Status'),
    'filter' => [
      'title' => t('Filter by Internal Status'),
      'help' => t('Bla bla'),
      'id' => 'internal_status_filter',
    ],
  ];
}

2-Create the filter plugin (In my case, I have extended the inOperator Class)

/**
 * Filters the internal status of a node
 *
 * @ingroup views_filter_handlers
 *
 * @ViewsFilter("internal_status_filter")
 */
class InternalStatus extends InOperator {

  public function init(ViewExecutable $view, DisplayPluginBase $display, array &$options = NULL) {
    parent::init($view, $display, $options);
    $this->valueTitle = t('Available Status');
    $this->definition['options callback'] = array($this, 'generateOptions');
  }

  public function query() {
    $configuration = [
      'left_table' => 'node_field_data',
      'left_field' => 'nid',
      'field'      => 'entity_id',
      'type'       => 'LEFT',
      'extra_operator'   => 'AND',
    ];
    if (
    $selected_status = $this->value[0];
    if ($this->operator == 'in') {
      $this->ensureMyTable();
      switch ($selected_status) {
        case (1):
          //Add a condition on another field
          $table_field='node__field_another_field';
          $configuration['table']=$table_field;
          $join = Views::pluginManager('join')->createInstance('standard', $configuration);
          $this->query->addRelationship($table_field, $join, 'node_field_data');
          $this->query->addWhere('AND', "${table_field}.field_simulation_is_past_value", 1, '=');
          //Add as many condition to get what you need
          break;
          ....
       }
     }
  }

  public function generateOptions() {
    return [
      1=>'Status 1',
      2=>'Status 2',
      3=>'Status 3',
    ];
  }

Make sure that your annotation at the top of the file @ViewsFilter("internal_status_filter") is the same id than the ones defined in hook_views_data_alter