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 "taxonomy_index"."sticky" AS "taxonomy_index_sticky", "taxonomy_index"."created" AS "taxonomy_index_created",
"node_field_data"."nid" AS "nid", "node_field_data"."langcode" AS "node_field_data_langcode"
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 = '14')) AND (("node_field_data"."langcode" IN ('nl', 'und', 'zxx'))
AND ("taxonomy_index"."status" = '1'))
ORDER BY "taxonomy_index_sticky" DESC, "taxonomy_index_created" DESC

I want, if applicable, extend the where clause so it becomes by example:

WHERE (((taxonomy_index.tid = '14')) AND ((("node_field_data"."langcode" IN ('en', 'und', 'zxx'))
AND ("taxonomy_index"."status" = '1')))
OR (("taxonomy_index"."tid" = '14') AND ("node_field_data"."nid" IN ('43'))
AND ("node_field_data"."langcode" 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->setWhereGroup('OR');
      $query->addWhere($group_id, 'taxonomy_index.tid', $tid);
      $query->addWhere($group_id, 'node_field_data.nid', $missing, 'IN');
      $query->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 ((("node_field_data"."langcode" IN ('en', 'und', 'zxx'))
AND ("taxonomy_index"."status" = '1'))
OR (("taxonomy_index"."tid" = '14') OR ("node_field_data"."nid" IN ('43'))
OR ("node_field_data"."langcode" NOT IN ('en', 'und', 'zxx'))))

But it should be an AND group within an OR Group, maybe like:


Who can help me out to achive this?

  • It sounds you just want that Views to not make any language limitations, to show all nodes in all languages for the particular taxonomy term. Is that what you want?
    – prkos
    Commented Apr 21, 2021 at 15:46

1 Answer 1


At first I found this function: $query->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.


 * @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->id() == 'taxonomy_term' && $view->current_display == 'page_1') {
    $language = \Drupal::languageManager()->getCurrentLanguage()->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' => 'NOT IN',
      'NOT IN' => 'IN',
    $expected_conditions = [
      // fieldname => expected operator.
      'node_field_data.langcode' => 'in',
      'taxonomy_index.tid' => 'formula',
      'taxonomy_index.status' => '=',
    // Traverse through the 'where' part of the query and save relevant data.
    foreach ($query->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'] = " AND taxonomy_index.status = '" . $values['status'] . "'";
    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 .= '<br />- ' . $err;

    // Get the current taxonomy tid value for queries.
    $values['tid'] = array_shift($values['tid']);
    // Substitute language placeholder with current language.
    foreach ($values['langcode'] as &$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->query("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 "
        . $in_operator . " (:langcodes[])" . $values['status'],
      [':tid' => $values['tid'], ':langcodes[]' => $values['langcode']])
    // Also for the other langauge.
    $missing_lang = $db->query("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 "
        . $in_reverse[$in_operator] . " (:langcodes[])" . $values['status'],
      [':tid' => $values['tid'], ':langcodes[]' => $values['langcode']])
    // Get missing nids.
    $missing = array_diff($missing_lang, $current_lang);
    // No nodes to add?
    if (empty($missing)) {

    // 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->where = [];
    // Create a new OR group using the original content.
    $group_id = $query->setWhereGroup('OR');
    $snippet = "taxonomy_index.tid = :tid AND node_field_data.langcode "
      . $in_operator . " (:langcodes[])" . $values['status'];
    $query->addWhereExpression($group_id, $snippet,
      [':tid' => $values['tid'], ':langcodes[]' => $values['langcode']]);
    $snippet = "taxonomy_index.tid = :tid AND node_field_data.nid IN (:missing[]) AND node_field_data.langcode "
      . $in_reverse[$in_operator] . " (:langcodes[])";
    $query->addWhereExpression($group_id, $snippet, [':tid' => $values['tid'],
      ':missing[]' => $missing, ':langcodes[]' => $values['langcode']]);
    // An alternative is to combine the two $snippets.
    // But this works: the OR statments are more clear.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.