Exposing Tables to Views in Drupal 7

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