How to set relationship between custom tables in Drupal views?
Here we are discussing how to implement an explicit relationship between two custom tables in views.
Here we are exposing two custom tables into views UI and using relationship, display all data of both tables in a view using relationship feature of view system.
We have below two custom tables in our Drupal database.
custom_primary_table
custom_secondary_table
In above custom_secondary_table you could see foreign key custom_primary_table_id which is the primary key values of custom_primary_table.
In next step we are exposing these two custom table and its columns to the view system.
Create view_data hook in your custom module .module file or *.views.inc file.
Here we are implementing view_data hook in file dn_viewtable.views.inc
Here dn_viewtable is the custom module name.
First start the view data hook with a data variable that needs to be returned at the end of the hook.
function dn_viewtable_views_data() {
$data = [];
return $data;
}
Then add your secondary table to the table array. Provide the group as ‘Secondary Table’ and Provider as your custom module machine name.
$data['custom_secondary_table'] = [];
$data['custom_secondary_table']['table'] = [];
$data['custom_secondary_table']['table']['group'] = t('Secondary table');
$data['custom_secondary_table']['table']['provider'] = 'dn_viewtable';
Also assign primary field for base table making secondary table as base table to views.
$data['custom_secondary_table']['table']['base'] = [
// Identifier (primary) field in this table for Views.
'field' => 'custom_primary_table_id',
// Label in the UI.
'title' => t('Secondary table'),
// Longer description in the UI. Required.
'help' => t('Secondary table contains example content and can be related to primary.'),
'weight' => -10,
];
Add id and score fields of secondary table as below.
//========add fields
$data['custom_secondary_table']['id'] = [
'title' => t(' ID field'),
'help' => t(' ID field.'),
'field' => [
'id' => 'numeric',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'numeric',
],
'argument' => [
'id' => 'numeric',
],
];
$data['custom_secondary_table']['score'] = [
'title' => t(' Customer Score field'),
'help' => t(' Customer Score field.'),
'field' => [
'id' => 'numeric',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'numeric ',
],
'argument' => [
'id' => 'numeric',
],
];
Add foreign key custom_primary_table_id as below with relationship array.
$data['custom_secondary_table']['custom_primary_table_id'] = [
'title' => t('Secondary content'),
'help' => t('Relate example content to the secondary content'),
'relationship' => [
// Views name of the table to join to for the relationship.
'base' => 'custom_primary_table',
// Database field name in the other table to join on.
'base field' => 'id',
// ID of relationship handler plugin to use.
'id' => 'standard',
// Default label for relationship in the UI.
'label' => t('Example Secondary'),
],
];
Here base is the primary table name and id is the primary table id.
In above steps we have exposed all fields of the secondary table to view UI with a relationship to the primary table.
In the next steps we are exposing custom_primary_table to view UI.
Assign the primary table to the table array with base field assignment. Provide group as ‘Primary Table’ and Provider as your custom module machine name.
$table_name = 'custom_primary_table';
$data[$table_name] = [];
$data[$table_name]['table'] = [];
$data[$table_name]['table']['group'] = t('Primary Table');
$data[$table_name]['table']['provider'] = 'dn_viewtable';
$data[$table_name]['table']['base'] = [
'field' => 'id',
'title' => t('Primary Base Table'),
'help' => t('All fields of ' . $table_name . '.'),
'weight' => -10,
];
Here field=> id is the primary key of custom_primary_table.
Then add all fields of custom_primary_table as below.
$data[$table_name]['id'] = [
'title' => t(' ID field'),
'help' => t(' ID field.'),
'field' => [
'id' => 'numeric',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'numeric',
],
'argument' => [
'id' => 'numeric',
],
];
$data[$table_name]['first_name'] = [
'title' => t(' Student Name field'),
'help' => t(' Student Name field.'),
'field' => [
'id' => 'standard',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'string ',
],
'argument' => [
'id' => 'string',
],
];
$data[$table_name]['last_name'] = [
'title' => t(' Last Name field'),
'help' => t(' Last Name field.'),
'field' => [
'id' => 'standard',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'string ',
],
'argument' => [
'id' => 'string',
],
];
$data[$table_name]['class'] = [
'title' => t(' Class field'),
'help' => t(' Class field.'),
'field' => [
'id' => 'standard',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'string ',
],
'argument' => [
'id' => 'string',
],
];
Please note there are no relationships in the primary table.
So our final view data hook would be as below.
/**
* Implements hook_views_data().
*/
function dn_viewtable_views_data() {
$data = [];
$data['custom_secondary_table'] = [];
$data['custom_secondary_table']['table'] = [];
$data['custom_secondary_table']['table']['group'] = t('Secondary table');
$data['custom_secondary_table']['table']['provider'] = 'dn_viewtable';
$data['custom_secondary_table']['table']['base'] = [
// Identifier (primary) field in this table for Views.
'field' => 'custom_primary_table_id',
// Label in the UI.
'title' => t('Secondary table'),
// Longer description in the UI. Required.
'help' => t('Secondary table contains example content and can be related to primary.'),
'weight' => -10,
];
//========add fields
$data['custom_secondary_table']['id'] = [
'title' => t(' ID field'),
'help' => t(' ID field.'),
'field' => [
'id' => 'numeric',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'numeric',
],
'argument' => [
'id' => 'numeric',
],
];
$data['custom_secondary_table']['score'] = [
'title' => t(' Customer Score field'),
'help' => t(' Customer Score field.'),
'field' => [
'id' => 'numeric',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'numeric ',
],
'argument' => [
'id' => 'numeric',
],
];
//============
$data['custom_secondary_table']['custom_primary_table_id'] = [
'title' => t('Secondary content'),
'help' => t('Relate example content to the secondary content'),
'relationship' => [
// Views name of the table to join to for the relationship.
'base' => 'custom_primary_table',
// Database field name in the other table to join on.
'base field' => 'id',
// ID of relationship handler plugin to use.
'id' => 'standard',
// Default label for relationship in the UI.
'label' => t('Example Secondary'),
],
];
//=========primary table=====
$table_name = 'custom_primary_table';
$data[$table_name] = [];
$data[$table_name]['table'] = [];
$data[$table_name]['table']['group'] = t('Primary Table');
$data[$table_name]['table']['provider'] = 'dn_viewtable';
$data[$table_name]['table']['base'] = [
'field' => 'id',
'title' => t('Primary Base Table'),
'help' => t('All fields of ' . $table_name . '.'),
'weight' => -10,
];
$data[$table_name]['id'] = [
'title' => t(' ID field'),
'help' => t(' ID field.'),
'field' => [
'id' => 'numeric',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'numeric',
],
'argument' => [
'id' => 'numeric',
],
];
$data[$table_name]['first_name'] = [
'title' => t(' Student Name field'),
'help' => t(' Student Name field.'),
'field' => [
'id' => 'standard',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'string ',
],
'argument' => [
'id' => 'string',
],
];
$data[$table_name]['last_name'] = [
'title' => t(' Last Name field'),
'help' => t(' Last Name field.'),
'field' => [
'id' => 'standard',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'string ',
],
'argument' => [
'id' => 'string',
],
];
$data[$table_name]['class'] = [
'title' => t(' Class field'),
'help' => t(' Class field.'),
'field' => [
'id' => 'standard',
],
'sort' => [
'id' => 'standard',
],
'filter' => [
'id' => 'string ',
],
'argument' => [
'id' => 'string',
],
];
//==========primary table=====
return $data;
}
Clear the cache and go the views listing page
Click on add view.
Provide view name as All custom data and in view settings select Secondary Table in show dropdown. And select Page check box. Create the view.
View has been created.
If you try to add to fields you could not see the custom_primary_table data here. See the below screen, only secondary fields are available.
In order to show primary table above, Go to view Relationship section in right side of the view and click on Add relationship.
Select relationship that we exposed in view data hook. In this case select Secondary content.
Select Require this relationship option and apply.
Now you can see primary table fields in add fields page.
Add all primary and secondary table fields.
Also select format as table, you could see the view page as below.
In this way we can combine results of two custom tables using view relationship feature.
