6

How should we alter a query of a view by using the hook_views_query_alter, by adding a new join table.

I tried

 $join = new views_join();

but it seems to be for drupal 7 not 8.

Please help.

3 Answers 3

13

Here is a solution to add a join table to an SQL query in views.

Let's consider a view for a Person entity. We want to add a join to a PersonTag entity. Person has an id field (the primary key); PersonTag has a person_id field that contains the PersonTag ID.

In your module_name.module, you would add this hook_views_query_alter() implementation.

function module_name_views_query_alter($view, $query) {
  if ($view->id() == 'views_id') {  
    $definition = [
      'table' => 'person_tag_field_data',
      'field' => 'person_id',
      'left_table' => 'person_field_data',
      'left_field' => 'id',
    ];
    $join = Drupal::service('plugin.manager.views.join')->createInstance('standard', $definition);
    $query->addRelationship('person_tag_field_data', $join, 'person'); 
  }
}

After clearing the cache, you could check the SQL query used by the view and notice INNER JOIN is added to the query.

module_name is the name of your module, views_id is the ID of your view, person_tag_field_data is the table name for the PersonTag entity, and person_field_data is the table name for the Person entity.

3
  • For INNER JOIN, remember to add 'type' => 'INNER', inside $definition
    – NKP
    Commented Oct 14, 2020 at 3:25
  • Just to be clear for left_table we should use table alias. And in my case for base variable of addRelationship method I've used also table alias. To check join correctness the one could enable query debugging in views to see query details on views edit page. Commented Jul 31, 2022 at 12:36
  • I would love to know enough to be able to modify queries in this hook, but it's so much more complicated and unreadable compared to using hook_views_pre_execute().
    – mbomb007
    Commented Aug 17, 2023 at 16:18
4

You can join table using:

/**
 * Implements hook_views_pre_execute().
 */
function YOUR_MODULE_views_pre_execute(\Drupal\views\ViewExecutable $view) {
  if ($view->id() == 'VIEWID' && $view->current_display == 'DISPLAY') {
    $input = $view->exposed_raw_input;
    // Views Query.
    $query = $view->build_info['query'];
    // Left Join any table here.
    $query->leftJoin('node__field_comp', 'node__field_comp', 'node_field_data.nid = node__field_comp.entity_id');
    // Add fields of table you joined.
    $query->fields('node__field_comp');
    $query->distinct();
    // Re-construct the Views Query by adding left join.
    $view->build_info['query'] = $query;
    $view->build_info['count_query'] = $query;
  }
}
2
  • can you explain more your code?
    – Asy
    Commented Feb 20, 2017 at 9:50
  • 2
    Added some comments..
    – Ajay Reddy
    Commented Feb 21, 2017 at 7:11
1

Note that the default join type is a LEFT join when 'type' is not supplied in the join plugin configuration.

For this SQL:

INNER JOIN {two} ON one.field_a = two.field_b AND one.field_c = 'some_val'

Use this configuration:

$configuration = array(
  'type' => 'INNER',
  'table' => 'two',
  'field' => 'field_b',
  'left_table' => 'one',
  'left_field' => 'field_a',
  'operator' => '=',
  'extra' => array(
    0 => array(
      'left_field' => 'field_c',
      'value' => 'some_val',
    ),
  ),
);
$join = Views::pluginManager('join')
  ->createInstance('standard', $configuration);

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.