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.
Drupal Answers is a question and answer site for Drupal developers and administrators. It only takes a minute to sign up.
Sign up to join this communityHow 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.
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.
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;
}
}
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);