How to add condition via views_query_alter() to filter a multi-value select field that does not contain a value?

quantumized

3/14/24, 8:26 PM

I have _views_query_alter() hook that I need to filter out nodes whose multi-value select does not contain a value ("National"). The code below works to show the nodes that do contain "National" but I need to do the opposite and cannot get it to work due the field being a multi-value field.

function HOOK_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {

  if ($view->id() == 'state_landing_page') {
    $field = 'field_resource_location_type';
    $value = 'National';
    $operator = '=';
    $query->addWhere('conditions', $field, $value, $operator);
  }
}

104

1 + 0

views

Score:2

Drupal

user106880

3/15/24, 5:00 AM

To filter out nodes that do not contain the value "National" in a multi-value select field, you can use the following code instead

function HOOK_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
      if ($view->id() == 'state_landing_page') {
        foreach ($query->where as &$condition_group) {
          foreach ($condition_group['conditions'] as &$condition) {
             if ($condition['field'] == 'node.field_resource_location_type') {
              $condition = [
                'field' => 'node.field_resource_location_type',
                'value' => 'National',
                'operator' => '<>',
              ];
            }
          }  
        }
      }
    }

The operator <> or != is used to exclude nodes that contain the value "National" in the field. This should work for multi-value fields as well. The <> operator and != operator are equivalent in MySQL, so either one will work in this case.

+ 3

quantumized

3/15/24, 12:37 PM

Thank you but I had tried this and is does not work as expected. If a node's Location Type field has "National" plus another value (Virtual, for example), then it still shows in the results. I can't get to work via the Views UI using filters either as their is no operator for the multi-value list for "Does not include"

quantumized

3/15/24, 2:35 PM

Thank you, your code works. The issue ended up being with the View being a Search API index that needed to be rebuilt for some reason and was causing it to not query properly.

Elon Musk

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

EN: How to add condition via views_query_alter() to filter a multi-value select field that does not contain a value?

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.