1

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.

5

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

Let's consider a views on an entity named :"Person", and that we want to add a join to another entity named "PersonTag" "Person" has a field named "id" (the primary key) "PersonTag" has a field named "person_id" (this field is referencing the id of "PersonTag" entity.

In your module_name.module, we will add this hook_views_query_alter():

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, we could check the sql query of the views and notice the ther is "INNER JOIN" added in the query.

PS: module_name = the name of your module

views_id = the id of your views

person_tag_field_data = the table name of the entity PersonTag

person_field_data = the table name of the entity Person

Hope it will help!

  • 1
    Thanks for this, I was on the verge of giving up on the documentation and using pre_execute hook to achieve something similar.. – optimusprime619 Mar 14 '17 at 13:04
  • I'm happy that helped you :) – Asy Mar 15 '17 at 13:25
1

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;
      }
    }
  • can you explain more your code? – Asy Feb 20 '17 at 9:50
  • 1
    Added some comments.. – Ajay Reddy Feb 21 '17 at 7:11

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.