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

Tobias Krause

4/11/24, 12:59 PM

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.

2 + 0

views

database

plugins

Score:1

Drupal

Chris4783

8/2/24, 12:43 PM

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

+ 2

Tobias Krause

8/7/24, 7:28 AM

I hoped to find an answer to my question as I run into this issue for several times now. Your answer does not actually answer my question but it solves the concrete problem well, I think. Thank you for this - and esspecially thank you for the service to prepare the code so well for me... It was simply copy and paste for me :)

Chris4783

8/7/24, 10:22 AM

If it is important for you to do this with a join, you can try some code like this ```$table_alias = 'ar'; $definition = [ 'field' => 'additional_role_target_id', 'left_table' => 'additional_role', 'left_field' => 'id', 'table' => 'additional_role', 'extra' => [ [ 'field' => 'deleted', 'value' => '0', ], ], ]; $join = \Drupal::service('plugin.manager.views.join')->createInstance('standard', $definition); $this->query->ensureTable($table_alias, $this->relationship, $join);``` Take a look at Drupal\views\Plugin\views\join\JoinPluginBase.php

Score:-1

Drupal

Vlad Moyseenko

8/2/24, 11:46 AM

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).

+ 2

Tobias Krause

8/7/24, 7:07 AM

This shifts the described problem just to another place and does not answer the question how the relationship can be added to the query. Additionally this idea would seperate code to different functions instead of using the advantage of OOP which is to have all related code bundled into one class... in this case a views filter plugin instead of two functions somewhere in the code.

Vlad Moyseenko

8/8/24, 10:01 AM

Well, @TobiasKrause I wish you luck. Thank you for -1. I based my answer on experience and not ephemeral "drupal way", despite I was advocate for this approach long time.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: 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

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.