Exposing Tables to Views in Drupal 7

Daniel Sipos
Daniel Sipos
Share

In this article you are going to learn how to expose the table created by your Drupal module to Views. Why would you like to do this? Views is a powerful module for querying and displaying information and can save you a lot of hassle when dealing with the custom data your module produces.

The focus of this tutorial will be on data that is not abstracted as Drupal entities, but simply stored in a table. Exposing custom entities to Views is a topic I covered in another Sitepoint.com series so I recommend reading up on that if this is the case for you – here is a list of my articles.

I set up a module called expose in this repository that you can clone, follow along and even build on if you want to learn more. The master branch contains a clean module that simply creates the table I will use in this article to illustrate the process. The exposed-table branch contains, commit by commit, the steps I go through below.

Since the schema definition is in the module, I will not repeat it here. I will however give you a short MySQL command to generate a couple of dummy records to have something to play with. Hopefully this command is informative enough to describe the table structure as well:

INSERT INTO `exposed` (`id`, `name`, `deadline`, `node_id`)
VALUES
	(1, 'Danny', 1399477939, 1),
	(2, 'Peter', 1399477957, 2);

Exposing the table to Drupal Views

There are two main steps we need to take to expose our exposed table to Views: make Views aware of our module and its Views related files and tell it about the structure of our table. The second step is the complex one where you can have various degrees of customization.

But first things first, the implementation of hook_views_api():

/**
 * Implements hook_views_api().
 */
function expose_views_api()
{
    return array(
        'api' => 3,
        'path' => drupal_get_path('module', 'expose') . '/includes/views',
    );
}

This is a very simple implementation in which we tell which Views API our module uses and the location of where the Views related include files will be placed. The hook_views_api() implementation goes in the .module file.

Next, it’s time to implement hook_views_data(), this time in a file called expose.views.inc located in the folder we specified above (/includes/views/):

/**
 * Implements hook_views_data().
 */
function expose_views_data()
{

    $data = array();

    $data['exposed']['table']['group'] = t('Exposed');

    $data['exposed']['table']['base'] = array(
        'title' => t('Exposed'),
        'help' => t('Contains records we want exposed to Views.'),
    );

    return $data;

}

With this implementation we tell Views all about our table in a big array that we return at the end. First, we need to specify the group our table will be part of. This will be used to identify where the table fields come from in the UI. Next, we tell Views that the exposed table is a base table (hence the base index). This means we can create a View that uses this table to start from – as the main query. And as you can see, for both pieces of information, the exposed key is actually the name of the table in the database.

These are the first two steps that will already allow us to see some changes in the UI. Clear the cache and add a new View. You’ll notice that next to Content, User, etc, you now have Exposed as well as a type of data. This is from when we defined the table as being a base one. However, if you continue and edit the View now, you’ll see a broken handler in the Fields section and none of the table columns can be found as fields. It follows to declare them to Views as well.

Fields

Now that Views knows about our table, let’s tell it also how to handle the table columns for displaying, filtering and sorting. The way to do this is by specifying some metadata about each column (like title and description) and which Views handler to use for this field for a given task. Let’s see how to do this for the first three columns on our table: id, name, deadline. Inside the hook_views_data() implementation, paste the following right before $data gets returned:

// The ID field
$data['exposed']['id'] = array(
    'title' => t('ID'),
    'help' => t('The record ID.'),
    'field' => array(
        'handler' => 'views_handler_field_numeric',
    ),
    'sort' => array(
        'handler' => 'views_handler_sort',
    ),
    'filter' => array(
        'handler' => 'views_handler_filter_numeric',
    ),
);

// The Name field
$data['exposed']['name'] = array(
    'title' => t('Name'),
    'help' => t('The record name.'),
    'field' => array(
        'handler' => 'views_handler_field',
    ),
    'sort' => array(
        'handler' => 'views_handler_sort',
    ),
    'filter' => array(
        'handler' => 'views_handler_filter_string',
    ),
);

// The Deadline field
$data['exposed']['deadline'] = array(
    'title' => t('Deadline'),
    'help' => t('The record deadline.'),
    'field' => array(
        'handler' => 'views_handler_field_date',
    ),
    'sort' => array(
        'handler' => 'views_handler_sort_date',
    ),
    'filter' => array(
        'handler' => 'views_handler_filter_date',
    ),
);

It seems like a lot but it’s not. For each field, we create a new array inside the exposed array (that is our new table), the key of which is being named after the table column. Then after some basic information (title and help), we tell Views which handler to use for each of these three operations: field, sort and filter. The first one is for displaying the field whereas the latter two are self explanatory.

But what is a handler? A handler is a Views class located in the handlers folder of the Views module that handles these operations. And here we basically need to specify which one of those classes should be used. For instance, for a regular integer field we would use the integer oriented handler for display, the regular one for sorting and again the integer one for filtering. For the deadline, we have date handlers. And if the default that Views offers is not enough (which can be the case), you can extend these classes, change some logic, and use the new custom handlers instead.

Now you can save, clear the cache and refresh your View. You should be able to add fields, filters and sort them. Views, meanwhile, knows how to handle each field depending on the data type they contain.

Joins

As you probably noticed, we have one more column in the table we did not yet cover: the node_id. This is an integer in the database and I suppose we can describe it as such. But it wouldn’t be very useful to create listings that show node IDs. Rather, these IDs should be used in a relationship (table join), and retrieve data from the respective nodes. To do this, we need 2 things: tell Views what other tables our table can join with and set the proper handlers for the node_id column.

First, let’s make sure Views knows that the exposed table can join with the node table. Inside the hook_views_date() implementation, before describing the fields, paste the following:

$data['exposed']['table']['join'] = array(
    'node' => array(
        'left_field' => 'nid',
        'field' => 'node_id',
    ),
);

So we basically add a new index join in which the array keys beneath represent the tables it will join with. The left_field is the field on the target table (node) while the field is the one on the current (exposed) table. This can be confusing since usually the column names are the same.

Next, we need to define the node_id field just like we did the rest, but with one extra bit of information, the relationship (and argument as well):

// The Node ID field
$data['exposed']['node_id'] = array(
    'title' => t('Node ID'),
    'help' => t('The record node ID.'),
    'field' => array(
        'handler' => 'views_handler_field_node',
    ),
    'sort' => array(
        'handler' => 'views_handler_sort',
    ),
    'filter' => array(
        'handler' => 'views_handler_filter_numeric',
    ),
    'relationship' => array(
        'base' => 'node',
        'field' => 'node_id',
        'handler' => 'views_handler_relationship',
        'label' => t('Node'),
    ),
    'argument' => array(
        'handler' => 'views_handler_argument_node_nid',
        'numeric' => TRUE,
        'validate type' => 'nid',
    ),
);

The display handler for this field is taken from the Views module implementation on behalf of the core Node module (found in the modules/node folder). This is the handler that is also used by Views when displaying the node ID on a View based on the node table.

The handlers for the filter and sort are the same as before since we are dealing with a regular integer. We can filter and sort on this as well. However, we also have a relationship key there, array through which we specify the join table, field and handler. views_handler_relationship is a class that will take care of the relationship for not only displaying fields from joined tables, but also the ability to filter and sort on them.

And finally, for good measure, I’ve also included the argument key through which we take care of handling arguments (contextual filters) based on the node ID. The handler used for this is again specific to the node ID, exactly as the Views module implements it. For other fields, you have a number of general argument handlers you can use as well.

So clear your cache and refresh the View. You should now be able to add a relationship based on the Node ID. Then, you will get a host of new node related fields that you can add, filter and sort on. Additionally, you can now add a contextual filter for this field and filter the View dynamically based on the ID passed in context.

Conclusion

As you can see, working with Views is easy both as a site builder and developer. Through a few simple steps, we managed to expose the data in our own custom table to Views. Now we can use it to display, filter, and sort our data, as well as access related Drupal entities. It’s a very pluggable system indeed, and if you are not happy with what the existing handlers can do for you, feel free to extend the ones that come close and adjust them for your data, then reference the new classes in the appropriate field definition. Good luck!

Frequently Asked Questions (FAQs) about Exposing Tables and Views in Drupal 7

What is the purpose of exposing tables and views in Drupal 7?

Exposing tables and views in Drupal 7 allows developers to create, manage, and display content in a flexible and efficient manner. By exposing tables, developers can directly interact with the database, enabling them to create custom queries and manipulate data as needed. On the other hand, views provide a user-friendly interface for displaying content in various formats such as lists, grids, slideshows, and more. This feature is particularly useful for non-technical users as it eliminates the need for complex coding.

How can I create a view in Drupal 7?

Creating a view in Drupal 7 involves several steps. First, navigate to the ‘Structure’ menu and select ‘Views’. Click on ‘Add new view’ and fill in the necessary details such as the view name, description, and the type of content you want to display. You can also specify the display format and the fields to be included in the view. Once you’ve configured the settings, click ‘Save’ to create the view.

Can I use MySQL views as entities for Drupal views?

Yes, you can use MySQL views as entities for Drupal views. This allows you to leverage the power of SQL to create complex queries and present the results in a Drupal view. However, this requires a good understanding of SQL and Drupal’s database API. It’s also important to note that changes made to the MySQL view will not be reflected in the Drupal view unless the view is refreshed.

What are the common issues encountered when working with views in Drupal 7?

Some common issues when working with views in Drupal 7 include performance issues due to complex queries, difficulties in customizing the view’s output, and challenges in managing access control. These issues can often be resolved by optimizing the query, using template files for customization, and setting appropriate permissions.

How can I optimize the performance of my views in Drupal 7?

Optimizing the performance of views in Drupal 7 can be achieved in several ways. One method is to limit the number of rows returned by the view. Another is to use caching to reduce the load on the database. You can also optimize the underlying SQL query by avoiding unnecessary joins and selecting only the necessary fields.

Can I use views in Drupal 8?

Yes, views are a core module in Drupal 8, meaning they are included by default in every Drupal 8 installation. The process of creating and managing views in Drupal 8 is similar to that in Drupal 7, but with some additional features and improvements.

How can I customize the output of my views in Drupal 7?

Customizing the output of views in Drupal 7 can be done using template files. These files allow you to control the HTML markup and CSS classes used by the view. You can also use the ‘Rewrite results’ feature to modify the output of individual fields.

How can I control access to my views in Drupal 7?

Access to views in Drupal 7 can be controlled using permissions. You can specify who can view, edit, and delete each view based on user roles. This can be configured in the ‘Access’ section of the view settings.

Can I expose custom tables to views in Drupal 7?

Yes, you can expose custom tables to views in Drupal 7. This requires implementing hook_views_data() in your module, which allows you to define how your table’s data should be represented in views.

What are the benefits of using views in Drupal 7?

Using views in Drupal 7 offers several benefits. It provides a user-friendly interface for displaying content, eliminates the need for complex coding, and allows for flexible content management. Views also support pagination, sorting, and filtering, making it easier for users to find and interact with your content.