0

Very new to web development and even newer to Drupal, and I have some questions that I would really appreciate if anyone could answer!

Here's my yml file, [Site name] is actually just the name of our site:

name: '[Site name] Query'
description: '[Site name] custom queries'
package: [Site name]
core: '8.x' 

Here's my .module file:

<?php

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


function ppc_query_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  $database = \Drupal::database();
  if ($view->id() == 'test_events') {
    $query = $database->query("SELECT node__field_date.field_date_value AS node__field_date_field_date_value,
      node_field_data.nid AS nid, DATE_FORMAT((node__field_date.field_date_value + INTERVAL -18000 SECOND), '%Y%m%d%H') AS node__field_date_field_date_value_hour
      FROM {node_field_data} node_field_data
      LEFT JOIN {node__field_date} node__field_date ON node_field_data.nid = node__field_date.entity_id AND node__field_date.deleted = '0'
      WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('event')) AND (node__field_date.field_date_value >= DATE(NOW()))
      ORDER BY node__field_date_field_date_value_hour DESC, node__field_date_field_date_value ASC
      LIMIT 4 OFFSET 0
");
  }
}

I copied and pasted the query that was already being run on my view and added some changes that the views module couldn't support.

However when I run this module on my site, nothing changes. How do I get my query alter function to work?

Thank you!

3
  • What query alter are you trying to do ? Checkout if this can help drupal.stackexchange.com/questions/272543/… – miststudent2011 Apr 11 at 0:26
  • @miststudent2011 I am trying to write a query that shows the next 4 events closest to today's date. if there aren't 4 future events, then it shows past events as well. is there a better way to do this? I don't believe the views gui allows me to show 4 events closest to todays date. – khadidja Apr 11 at 0:59
  • If you have named the hook properly and enabled your custom module, then the hook will run. If it doesn't then the most likely reason is you've named it wrong. Have you ever written a working hook before? For ppc_query_views_query_alter() your module must be named ppc_query.module and your yml file should be named ppc_query.info.yml and must be in the same directory as your .module file. You should enable your module at /admin/build/modules. – anonymous Apr 11 at 2:26
0

Nothing changes because you are setting a hook argument, but the value to which you set that argument isn't kept.

hook_views_query_alter() is invoked from Sql::alter(), which is called from ViewsExecutable::build().

// ViewsExecutable::build()
// Let modules modify the query just prior to finalizing it.
$this->query->alter($this);
// Sql::alter()
\Drupal::moduleHandler()->invokeAll('views_query_alter', [$view, $this]);

The only way to alter the query is using the methods made available from QueryPluginBase (including its parent classes), not replacing an object passed to the hook with another object. See for example workspaces_views_query_alter(), an implementation of that hook done from a Drupal core module. The relevant code altering the query is the following one (ViewsQueryAlter::alterQuery() and ViewsQueryAlter::alterQueryForEntityType()). (I copied only the comments that could help in understanding the changes done to the query.)

public function alterQuery(ViewExecutable $view, QueryPluginBase $query) {
  if (!$this->workspaceManager->hasActiveWorkspace()) {
    return;
  }

  if (!$query instanceof Sql) {
    return;
  }

  $entity_type_ids = [];
  foreach ($query->relationships as $info) {
    $table_data = $this->viewsData->get($info['base']);
    if (empty($table_data['table']['entity type'])) {
      continue;
    }
    $entity_type_id = $table_data['table']['entity type'];

    $entity_type_ids[$entity_type_id] = $entity_type_id;
  }
  $entity_type_definitions = $this->entityTypeManager->getDefinitions();
  foreach ($entity_type_ids as $entity_type_id) {
    if ($this->workspaceManager->isEntityTypeSupported($entity_type_definitions[$entity_type_id])) {
      $this->alterQueryForEntityType($query, $entity_type_definitions[$entity_type_id]);
    }
  }
}

protected function alterQueryForEntityType(Sql $query, EntityTypeInterface $entity_type) {
  $table_mapping = $this->entityTypeManager->getStorage($entity_type->id())->getTableMapping();
  $field_storage_definitions = $this->entityFieldManager->getFieldStorageDefinitions($entity_type->id());
  $dedicated_field_storage_definitions = array_filter($field_storage_definitions, function ($definition) use ($table_mapping) {
    return $table_mapping->requiresDedicatedTableStorage($definition);
  });
  $dedicated_field_data_tables = array_map(function ($definition) use ($table_mapping) {
    return $table_mapping->getDedicatedDataTableName($definition);
  }, $dedicated_field_storage_definitions);
  $move_workspace_tables = [];
  $table_queue =& $query->getTableQueue();
  foreach ($table_queue as $alias => &$table_info) {
    if ($table_info['table'] == 'workspace_association') {
      break;
    }

    if ($field_name = array_search($table_info['table'], $dedicated_field_data_tables, TRUE)) {
      $relationship = $table_info['relationship'];

      if ($table_info['join']->field != 'entity_id') {
        continue;
      }

      $new_table_name = $table_mapping->getDedicatedRevisionTableName($field_storage_definitions[$field_name]);

      $workspace_association_table = $this->ensureWorkspaceAssociationTable($entity_type->id(), $query, $relationship);

      // Update the join to use our COALESCE.
      $revision_field = $entity_type->getKey('revision');
      $table_info['join']->leftFormula = "COALESCE({$workspace_association_table}.target_entity_revision_id, {$relationship}.{$revision_field})";

      // Update the join and the table info to our new table name, and to join
      // on the revision key.
      $table_info['table'] = $new_table_name;
      $table_info['join']->table = $new_table_name;
      $table_info['join']->field = 'revision_id';

      // Finally, if we added the workspace_association table we have to move
      // it in the table queue so that it comes before this field.
      if (empty($move_workspace_tables[$workspace_association_table])) {
        $move_workspace_tables[$workspace_association_table] = $alias;
      }
    }
  }

  // JOINs must be in order. i.e, any tables you mention in the ON clause of a
  // JOIN must appear prior to that JOIN. Since we're modifying a JOIN in
  // place, and adding a new table, we must ensure that the new table appears
  // prior to this one. So we recorded at what index we saw that table, and
  // then use array_splice() to move the workspace_association table join to
  // the correct position.
  foreach ($move_workspace_tables as $workspace_association_table => $alias) {
    $this->moveEntityTable($query, $workspace_association_table, $alias);
  }
  $base_entity_table = $entity_type->isTranslatable() ? $entity_type->getDataTable() : $entity_type->getBaseTable();
  $base_fields = array_diff($table_mapping->getFieldNames($entity_type->getBaseTable()), [
    $entity_type->getKey('langcode'),
  ]);
  $revisionable_fields = array_diff($table_mapping->getFieldNames($entity_type->getRevisionDataTable()), $base_fields);

  // Go through and look to see if we have to modify fields and filters.
  foreach ($query->fields as &$field_info) {
    // Some fields don't actually have tables, meaning they're formulae and
    // whatnot. At this time we are going to ignore those.
    if (empty($field_info['table'])) {
      continue;
    }

    // Dereference the alias into the actual table.
    $table = $table_queue[$field_info['table']]['table'];
    if ($table == $base_entity_table && in_array($field_info['field'], $revisionable_fields)) {
      $relationship = $table_queue[$field_info['table']]['alias'];
      $alias = $this->ensureRevisionTable($entity_type, $query, $relationship);
      if ($alias) {
        // Change the base table to use the revision table instead.
        $field_info['table'] = $alias;
      }
    }
  }
  $relationships = [];

  // Build a list of all relationships that might be for our table.
  foreach ($query->relationships as $relationship => $info) {
    if ($info['base'] == $base_entity_table) {
      $relationships[] = $relationship;
    }
  }

  // Now we have to go through our where clauses and modify any of our fields.
  foreach ($query->where as &$clauses) {
    foreach ($clauses['conditions'] as &$where_info) {
      // Build a matrix of our possible relationships against fields we need
      // to switch.
      foreach ($relationships as $relationship) {
        foreach ($revisionable_fields as $field) {
          if (is_string($where_info['field']) && $where_info['field'] == "{$relationship}.{$field}") {
            $alias = $this->ensureRevisionTable($entity_type, $query, $relationship);
            if ($alias) {
              // Change the base table to use the revision table instead.
              $where_info['field'] = "{$alias}.{$field}";
            }
          }
        }
      }
    }
  }
}

The code is also wrong because it replaces an instance of a class that extends QueryPluginBase with an instance of a class that implements StatementInterface. Those objects aren't interchangeable, as their methods and properties are different.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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