How to configure custom tables in Drupal views?

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

MYSQL Command

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