2

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.

8

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.

| improve this answer | |
  • For INNER JOIN, remember to add 'type' => 'INNER', inside $definition – NKP Oct 14 at 3:25
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;
  }
}
| improve this answer | |
  • can you explain more your code? – Asy Feb 20 '17 at 9:50
  • 2
    Added some comments.. – Ajay Reddy Feb 21 '17 at 7:11
0

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);
| improve this answer | |

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.