How do I add a query condition to the QueryPluginBase in hook_views_query_alter()?

Question

Score:2

Drupal

How do I add a query condition to the QueryPluginBase in hook_views_query_alter()?

Joseph Scoggins

12/30/23, 11:49 PM

Using hook_views_query_alter() for a view configured as a REST feed, I'm attempting to add an additional filter value to the query, based on the presence of multiple filters passed via the URL.

I need ensure a URL query like /my-feed/?field_my_term_target_id=2761,3101 returns content tagged with 836, for example, as well as the other two terms. So, figuratively speaking, my task is adding or field_my_term_target_id = some_tag_id to a correctly configured query object.

An instance of the Sql object is being passed to the hook as QueryPluginBase; how do I properly use it and its Condition object to add a term to the query?

Although there are a number of posts on the use of hook_views_query_alter(), or on adding conditions to a query, few are recent (I'm using Drupal 9.4) and none addresses this particular circumstance. I found similar techniques employed in How can I add multiple conditions for the same field taxonomy term in views query?, but I haven't gained insight on how to utilize them here.

I should mention my objective is to look up the child terms of the taxonomy values passed as query variables via the URL, and add those to the query. The lookup part is trivial, but the process of adding terms is blocking my progress.

For the following example code in my_module.module, I am attempting to add a single hard-coded value for proof of concept. I am expecting a query variable with multiple values in the URL, like /my-feed/?field_my_term_target_id=2761,3101.

use Drupal\views\Plugin\views\query\Sql;
use Drupal\views\Plugin\views\query\QueryPluginBase;
use Drupal\views\Plugin\views\join\JoinPluginBase;
use Drupal\Core\Database\Query\Condition;

function my_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'my_feed') {
    if ($query instanceof Sql) {

      $this_query = &$query;

      // Here we assume that node type and publication status are conditions [0] and [1]
      if (isset($this_query->where[1]['conditions'][2]['field']) &&
        $this_query->where[1]['conditions'][2]['field'] instanceof Condition) {

        $join = new joinPluginBase(
          [
           'table' => 'node__field_my_term',
           'type' => 'LEFT',
           'left_field' => 'node_field_data.nid',
           'field' => 'entity_id',
           'operator' => '='
          ],
          'node__field_my_term_value_2',
          'desc'
        );

        $this_query->addRelationship(
          'node__field_my_term_value_2', // Table name / relationship name
          $join,                         // the configured joinPluginBase
          'node__field_my_term'          // The base table
        );

        $this_query->addTable(
          'node__field_my_term',         // existing table name in global namespace
          NULL,                          // Relationship: If set to NULL the path to the primary table will be ensured
          $join,                         // the configured joinPluginBase
          'node__field_my_term_value_2'  // override the default alias suffix, e.g. '_value_03'
        );

        $this_query->addField(
          'node__field_my_term_value_2',   
          'field_my_term_target_id'     
        );

        $test_tag_id = '836';

        $this_query->where[1]['conditions'][2]['field']->condition(
          'node__field_my_term_value_2.field_my_term_target_id',
          $test_tag_id,
          '='
        );
      }
    }
  }
}

In $this_query->tables the added table appears to match convention.

Array(
  [node_field_data] => Array(
    [node_field_data] => Array(
      [count] => 1
      [alias] => node_field_data
    )
    [node__field_my_term] => Array(
      [count] => 2
      [alias] => node__field_my_term_value_0
    )
    [node__field_my_term_value_0] => Array(
      [count] => 1
      [alias] => node__field_my_term_value_0
    )
    [node__field_my_term_value_1] => Array(
      [count] => 1
      [alias] => node__field_my_term_value_1
    )
    // the manually added table
    [node__field_my_term_value_2] => Array(
      [count] => 1
      [alias] => node__field_my_term_value_2
    )
  )
)

Compared with the auto-generated tables in the data structure, the manually-created table lacks an argument as well as the additional AND statement in its condition.

How can I utilize the Sql object to generate a properly appended query? How do I add the missing AND condition to the LEFT JOIN rule and pass it the added value as an argument?

The altered query doesn't result in an error message but it produces unusable feed data:

[{"nid":"31891","field_event_datetime_range_all":"{\u0022value\u0022:\u0022{\\\u0022value\\\u0022:\\\u0022{\\\\\\\u0022value\\\\\\\u0022:\\\\\\\u0022{\\\\\\\\\\\\\\\u0022value\\\\\\\\\\\\\\\u0022:\\\\\\\\\\\\\\\u0022{\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\u0022value\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\u0022:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\u0022{

256

0 + 0

views

taxonomy-terms

hooks

How do I add a query condition to the QueryPluginBase in hook_views_query_alter()?

Post an answer