alter query view by adding fields in an andGroup within an orGroup I have a multilanguage site with two languages: English and Dutch.
Not all nodes are created in both languages. Some nodes even have no language ('und', 'zxx').
I want the default Drupal provided taxonomy view output all nodes of a taxonomy-term in the current and no node-language, AND, if present, other nodes of this term from the other language.
The Drupal provided sql is:
SELECT &quot;taxonomy_index&quot;.&quot;sticky&quot; AS &quot;taxonomy_index_sticky&quot;, &quot;taxonomy_index&quot;.&quot;created&quot; AS &quot;taxonomy_index_created&quot;,
&quot;node_field_data&quot;.&quot;nid&quot; AS &quot;nid&quot;, &quot;node_field_data&quot;.&quot;langcode&quot; AS &quot;node_field_data_langcode&quot;
FROM {node_field_data} &quot;node_field_data&quot;
LEFT JOIN {taxonomy_index} &quot;taxonomy_index&quot; ON node_field_data.nid = taxonomy_index.nid
WHERE ((taxonomy_index.tid = '14')) AND ((&quot;node_field_data&quot;.&quot;langcode&quot; IN ('nl', 'und', 'zxx'))
AND (&quot;taxonomy_index&quot;.&quot;status&quot; = '1'))
ORDER BY &quot;taxonomy_index_sticky&quot; DESC, &quot;taxonomy_index_created&quot; DESC
LIMIT 11 OFFSET 0

I want, if applicable, extend the where clause so it becomes by example:
WHERE (((taxonomy_index.tid = '14')) AND (((&quot;node_field_data&quot;.&quot;langcode&quot; IN ('en', 'und', 'zxx'))
AND (&quot;taxonomy_index&quot;.&quot;status&quot; = '1')))
OR ((&quot;taxonomy_index&quot;.&quot;tid&quot; = '14') AND (&quot;node_field_data&quot;.&quot;nid&quot; IN ('43'))
AND (&quot;node_field_data&quot;.&quot;langcode&quot; NOT IN ('en', 'und', 'zxx'))))

I wrote a function example_views_query_alter(ViewExecutable $view, QueryPluginBase $query) in which I found out which extra nodes should be added in the extra OR clause. By adding this code:
      $group_id = $query-&gt;setWhereGroup('OR');
      $query-&gt;addWhere($group_id, 'taxonomy_index.tid', $tid);
      $query-&gt;addWhere($group_id, 'node_field_data.nid', $missing, 'IN');
      $query-&gt;addWhere($group_id, 'node_field_data.langcode', $langcodes2, 'NOT IN');

But in the extended where clause I get OR instead of AND statements:
WHERE ((taxonomy_index.tid = '14')) AND (((&quot;node_field_data&quot;.&quot;langcode&quot; IN ('en', 'und', 'zxx'))
AND (&quot;taxonomy_index&quot;.&quot;status&quot; = '1'))
OR ((&quot;taxonomy_index&quot;.&quot;tid&quot; = '14') OR (&quot;node_field_data&quot;.&quot;nid&quot; IN ('43'))
OR (&quot;node_field_data&quot;.&quot;langcode&quot; NOT IN ('en', 'und', 'zxx'))))

But it should be an AND group within an OR Group, maybe like:
$query-&gt;setWhereGroup('OR');
$query-&gt;setWhereGroup('AND');
$query-&gt;addWhere(...

Who can help me out to achive this?
At first I found this function: $query-&gt;addWhereExpression(...) which allows me to write the query code myself.
I kept the problem the original, by views generated code, always results in this pseudocode:
cond1 AND (cond2 AND cond3)
The needed pseudoce shoudl be:
(cond1 AND cond2 AND cond3)
And I have to add an OR clause: (cond1 AND cond2 AND cond3)
In total giving this code:
(cond1 AND cond2 AND cond3) OR (cond4 AND cond5 AND cond6)
See my full working function below.
I hope others can benefit of this function and developpers will be inspired by the code. Currently there are only few examples and even less documentation.
&lt;?php

/**
 * @file
 * example module file to alter the Drupal suuplied taxonomy_term view by adding nodes from the non-current language to the current language, avoiding duplicates based on the nid.
 */

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query;
use Drupal\views\Plugin\views\query\QueryPluginBase;

/**
 * Implements hook_views_query_alter().
 *
 * Alters the taxonomy_term view.
 * Only valid for sites with two languages.
 * Add nodes of the other language to the view if they have a different nid.
 *
 * @param \Drupal\views\ViewExecutable $view
 * @param \Drupal\views\Plugin\views\query\QueryPluginBase $query
 */
function example_views_query_alter(ViewExecutable $view, QueryPluginBase $query){
  if ($view-&gt;id() == 'taxonomy_term' &amp;&amp; $view-&gt;current_display == 'page_1') {
    $language = \Drupal::languageManager()-&gt;getCurrentLanguage()-&gt;getId();
    // If the view should not be modified for a specific language.
    // if ($language == 'en') {
    //   return;
    // }

    // Step 1.
    // Gather info about the current views query.
    // There are a lot of checks because we will be sure the information about
    // the current query can be interpreted and changed:
    // - an administrator can change the view
    // - drupal can change the implementation in a future version
    // - another hook_..._alter can change the query as well.
    $values = $errors = [];
    $in_operator = 'IN';
    $in_reverse = [
      'IN' =&gt; 'NOT IN',
      'NOT IN' =&gt; 'IN',
    ];
    $expected_conditions = [
      // fieldname =&gt; expected operator.
      'node_field_data.langcode' =&gt; 'in',
      'taxonomy_index.tid' =&gt; 'formula',
      'taxonomy_index.status' =&gt; '=',
    ];
    // Traverse through the 'where' part of the query and save relevant data.
    foreach ($query-&gt;where as $condition_group) {
      foreach ($condition_group['conditions'] as $condition) {
        // The $condition['field'] can contain several 'words'.
        $parts = explode(' ', $condition['field']);
        $fieldname = $parts[0];
        // Do we expect this fieldname?
        if (isset($expected_conditions[$fieldname])) {
          $parts = explode('.', $fieldname);
          // For our conveniance we use the short table names.
          $values[$parts[1]] = $condition['value'];
          if (strcasecmp($expected_conditions[$fieldname], $condition['operator']) != 0) {
            $error = TRUE;
            if ($expected_conditions[$fieldname] == 'in') {
              if (strtolower($condition['operator']) == 'not in') {
                $expected_conditions[$fieldname] = $condition['operator'];
                $error = FALSE;
              }
              $in_operator = strtoupper($expected_conditions[$fieldname]);
            }
            if ($error) {
              $errors[] = 'Unexpected operator ' . $condition['operator']
                . ' in field: ' . $fieldname
                . '. Expected: ' . $expected_conditions[$fieldname];
            }
          }
        }
        else {
          $errors[] = 'Unexpected condition field: ' . $fieldname;
        }
      }
    }

    // Step 2.
    // All values are transformed for the queries.
    // All expected conditions found?
    // Status may be absent / create the query part for status.
    if (!isset($values['status'])) {
      $values['status'] = '';
    }
    else {
      $values['status'] = &quot; AND taxonomy_index.status = '&quot; . $values['status'] . &quot;'&quot;;
    }
    if (count($expected_conditions) != count($values)) {
      $errors[] = 'One or more conditions are missing.';
    }
    // If errors found, log these and quit. Better safe then sorry.
    if (!empty($errors)) {
      $message = 'Error in function example_views_query_alter() to alter view taxonomieterm.';
      foreach ($errors as $err) {
        $message .= '&lt;br /&gt;- ' . $err;
      }
      \Drupal::logger('example')-&gt;critical($message);
      return;
    }

    // Get the current taxonomy tid value for queries.
    $values['tid'] = array_shift($values['tid']);
    // Substitute language placeholder with current language.
    foreach ($values['langcode'] as &amp;$lc) {
      $lc = str_replace('***LANGUAGE_language_interface***', $language, $lc);
    }

    // Step 3.
    // Lookup if the other language has additional nodes.
    // Find all nids for the current node langages.
    $db = \Drupal::database();
    $current_lang = $db-&gt;query(&quot;SELECT node_field_data.nid AS nid
      FROM {node_field_data} node_field_data
      LEFT JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
      WHERE taxonomy_index.tid = :tid AND node_field_data.langcode &quot;
        . $in_operator . &quot; (:langcodes[])&quot; . $values['status'],
      [':tid' =&gt; $values['tid'], ':langcodes[]' =&gt; $values['langcode']])
      -&gt;fetchCol();
    // Also for the other langauge.
    $missing_lang = $db-&gt;query(&quot;SELECT node_field_data.nid AS nid
      FROM {node_field_data} node_field_data
      LEFT JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
      WHERE taxonomy_index.tid = :tid AND node_field_data.langcode &quot;
        . $in_reverse[$in_operator] . &quot; (:langcodes[])&quot; . $values['status'],
      [':tid' =&gt; $values['tid'], ':langcodes[]' =&gt; $values['langcode']])
      -&gt;fetchCol();
    // Get missing nids.
    $missing = array_diff($missing_lang, $current_lang);
    // No nodes to add?
    if (empty($missing)) {
      return;
    }

    // Step 4.
    // Change the where clause of the query.
    // We like to add only an OR group with the nids to add.
    // The current where clause creates the following pseudo code:
    // WHERE cond1 AND (cond2 AND cond3)
    // But we need:
    // WHERE (cond1 AND cond2 AND cond3)
    // No way was found to change the current query to the needed clause.
    // The goal is to expand the where clause with this result:
    // WHERE (cond1 AND cond2 AND cond3) OR (cond4 AND cond5 AND cond6)
    // So recreate the whole where clause.
    $query-&gt;where = [];
    // Create a new OR group using the original content.
    $group_id = $query-&gt;setWhereGroup('OR');
    $snippet = &quot;taxonomy_index.tid = :tid AND node_field_data.langcode &quot;
      . $in_operator . &quot; (:langcodes[])&quot; . $values['status'];
    $query-&gt;addWhereExpression($group_id, $snippet,
      [':tid' =&gt; $values['tid'], ':langcodes[]' =&gt; $values['langcode']]);
    $snippet = &quot;taxonomy_index.tid = :tid AND node_field_data.nid IN (:missing[]) AND node_field_data.langcode &quot;
      . $in_reverse[$in_operator] . &quot; (:langcodes[])&quot;;
    $query-&gt;addWhereExpression($group_id, $snippet, [':tid' =&gt; $values['tid'],
      ':missing[]' =&gt; $missing, ':langcodes[]' =&gt; $values['langcode']]);
    // An alternative is to combine the two $snippets.
    // But this works: the OR statments are more clear.
  }
}