Blogs | Srijan

How to configure custom tables in Drupal views? - Srijan

Written by Rashmi Soni | Jul 19, 2022 8:50:02 AM

The view is a powerful module for querying and displaying information and can save you a lot of hassle when dealing with the custom data produced by Drupal modules. Drupal views make it easy to query/display data, including custom table data.  In this article, you are going to learn how to expose the table created by your Drupal module to Views. 

The focus of this tutorial will be on data that is not abstracted as Drupal entities but simply stored in a table. 

To help Drupal views understand the exposure of modules and custom table data, we use hook_views_data().

Drupal 8 and Drupal 9 support  Views Custom Table. And alternatively, we can use hook_views_data to achieve our goal. But the Views custom table already uses hook_view_data to add custom tables in views. So, it will extend the functionality.

Let's start with the Views Custom table.

The view custom table module provides the functionality to integrate your custom table data to view and access all its columns in the custom views. This module offers you the following functionalities.

  1. Auto integrate custom table data to views.
  2. Auto map MySQL data types with Drupal data type.
  3. Extend the relationship of custom table data to Drupal entities.

How to use custom view table

  1. Download and extract files in the module folder.
  2. Enable module from the module list
  3. Go to Home » Administration » Structure » Views » View Custom Table
  4. Add your custom table to the system using the "Add Custom Table" link
  5. Add Relationship with Drupal entities if any, otherwise, leave none.
  6. Clear Drupal cache
  7. Add a new view, and you will see your custom table name in the "Show" drop-down field.

Detailed configuration views with custom table

Here's a demo using a user entity with a custom table. 

Step 1

Login with administrator -> Navigate to people -> Add some people data.

Step 2

Create a Custom table  

  • Each custom table must have a primary key.
  • Columns related to the Drupal entity must be numeric.

Note: The schema definition is already in the module. Therefore, let's not repeat here again. 

Here, I've modified the table structure. I will give you a short MySQL command & MYSQL definition to generate a couple of dummy records to play with it. Hopefully, this command is informative enough to describe the table structure as well:

MYSQL definition

CREATE TABLE `user__ct` (
  `bundle` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `entity_id` int(10) UNSIGNED NOT NULL COMMENT 'The entity id this data is attached to',
  `uid` int(10) UNSIGNED NOT NULL COMMENT 'The entity revision id this data is attached to, which for an unversioned entity type is the same as the entity id',
  `ct_target_id` varchar(255) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Data storage for user field roles.';

 

ALTER TABLE `user__ct`
  ADD PRIMARY KEY (`entity_id`,`uid`),
  ADD KEY `bundle` (`bundle`),
  ADD KEY `ct_target_id` (`ct_target_id`);
COMMIT;

 

MYSQL Command

INSERT INTO `user__ct` (`bundle`, `entity_id`, `uid`, `ct_target_id`) VALUES
('user', 1, 1, '100'),
('user', 2, 2, '20000'),
('user', 3, 3, '30000');



Step 3

Go to Home » Administration » Structure » Views » View Custom Table

Step 4

Add your custom table to the system using the "Add Custom Table" link.

 
Step 5

Add Relationships with Drupal entities if any, otherwise, leave none.

The custom demo table has a user uid relationship. Therefore, I added a relation to the column entity_id and uid table. If you do not want to give a specific relationship, then don’t keep any.
Step 6

Clear the Drupal cache

Step 7

Add a new view. You will see your custom table name in the "Show" drop-down field.

Here we are exploring two demos: 

  1. How to auto integrate custom table data to views /Auto map MySQL data types with the Drupal data type.
  2. How to extend the relationship of custom table data to Drupal entities

Demo 1 - How to auto integrate custom table data to views /Auto map MySQL data types with the Drupal data type

 

 

Demo 2 - How to extend the relationship of custom table data to Drupal entities

 
View full post