Custom views filter plugin: filter over two fields of a custom entity where one of the fields is a multi value field in a seperate table

Question

I have a custom entity with two basefields of type "list_string": "role" and "additional_role". I need this as "role" is used for sorting the entities by a very specific logical order (like the "boss" comes first and the "team lead" after the "boss"). The "role" basefield is a simple one-value field which is stored in the entity table in one column but the "additional_roles" is a multi value field which is therefore stored in a seperate table "my_entity__additional_roles".

What I now want to have is a custom (exposed) filter plugin which allows the user to choose from a list of roles. The result should be filtered over both fields so that when the role "developer" is chosen every entity with this value in "role" OR in "additional_roles" is listed.

So far I have overwritten the views filter plugin for the "role" field to some custom filter plugin which extends InOperator. My idea was to overwrite the method opSimple() - I did it that way for some other custom filter plugins but there I always was able to filter with fields which are all in the same entity table. For example I checked for a date range over two date fields of another entity:

  public function query() {
    $this->ensureMyTable();


    if ($this->value && isset($this->value[0]) && $this->value[0] == 1) {
      $date = $this->getDate();
      if (!$date) {
        $date = date('Y-m-d');
      }
      $this->query->addWhere($this->options['group'], (new Condition('OR'))
        ->condition('end_date', '', 'IS NULL')
        ->condition('end_date', $date, '>='));

      $this->query->addWhere($this->options['group'], (new Condition('OR'))
        ->condition('start_date', '', 'IS NULL')
        ->condition('start_date', $date, '<='));
    }
  }

But know I fail in getting the relationship for the multi value field into the views query. My idea is to adjust the query in a way that I could add such an OR subquery including a condition involving the value from the multi value field table.

Answer 1

I think the easiest way would be to get all corresponding entity ids to this role and filter the query based on this ids. You can override the opSimple method of the inOperator for example with the following code:

protected function opSimple() {
  if (empty($this->value)) {
    return;
  }
  $this->ensureMyTable();

  // Get all corresponding entities of this role.
  $ids = $query = \Drupal::entityTypeManager()->getStorage('custom_entity_type')->getQuery();
  $or_condition = $query->orConditionGroup();
  $or_condition->condition('role', $this->value)
    ->condition('additional_role', $this->value);
  $query->condition($or_condition);
  $query->accessCheck(FALSE);
  $ids = $query->execute();

  if (empty($ids)) {
    // If we haven`t found some entities, we have no results.
    $this->query->addWhereExpression($this->options['group'], '1 != 1');
    return;
  }

  if ($this->operator == 'in') {
    $this->query->addWhereExpression($this->options['group'], "$this->tableAlias.id IN (:ids[])", [':ids[]' => $ids]);
  }
  elseif ($this->operator == 'not in') {
    $this->query->addWhereExpression($this->options['group'], "$this->tableAlias.id NOT IN (:ids[])", [':ids[]' => $ids]);
  }
  else {
    // Adjust if you have other operators.
  }
}

Answer 2

Here is a quick tip. I recommend do not spend time on "fighting" with views and separate this feature into UI and Query parts.

• UI element: you can add/adjust it in hook_form_views_exposed_form_alter().
• Query part: any suitable views query hooks or hook_query_TAG_alter() (just add specific query tag in views display settings).