How to add a new join table by using a hook view?

Question

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.

Answer 1

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.

Answer 2

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;
  }
}

Answer 3

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);