Describing tables to Views

Tables are described to Views via hook_views_data(), which returns an array of table information, keyed by the name of the table. For example, if your module is describing three tables, 'foo', 'bar' and 'baz', your array will look like this:

$data = array(
  'foo' => array(
    // ...info here...
  ),
  'bar' => array(
    // ...info here...
  ),
  'baz' => array(
    // ...info here...
  ),
);

The key should be the actual database name of the table (not including prefix), but it can be an alias as long as the join information (explained later) contains the real name of the table.

Each item in the array should be a field in the table, with the exception of a special information section called 'table'. Example:

$data['foo'] = array(
  'table' => array(
    // ... info about the table, described later ...
  ),
  'bar' => array(
    // ... info about the field named 'bar', i.e, foo.bar,
  ),
  'baz' => array(
    // ... info about the field named 'baz', i.e, foo.baz,
  ),
);

Once you get down to an array that contains actual data, that piece of the array will often be referred to as the definition.

The 'table' section

Each table should have a 'table' section in it, which is used to set default information for the table, such as the group, as well as the very important joins and whether or not this is a base table.

First, there are several items that are actually for fields but can be placed here so that all fields within the table inherit them:

group: The name of the group this item will be with. In the UI, this is displayed as Group: Title. For example, "Node: Node ID", "Taxonomy: Term description", etc. It is important to be consistent with groups, because the UI sorts by group, and allows filtering by group to find fields as well.
title: The actual name of the field; it should be concise and descriptive.
help: A longer description to help describe what the field is or does. It should try to be only a line or two so as not to clutter the UI.

In general, having 'title' and 'help' at the table level doesn't make a lot of sense, but usually every item in a table is in the same group. Thus it is very common to define the 'group':

  $data['foo']['table']['group'] = t('Foo');

The other items in the 'table' section are described in the following sections.

'base': Base table

If your table is a base table -- meaning it can be the primary, central table for a View to use, you can declare it to be a base table. This primarily provides UI information so that it can be selected.
For example:

  // Advertise this table as a possible base table
  $data['node']['table']['base'] = array(
    'field' => 'nid',
    'title' => t('Node'),
    'help' => t("Nodes are a Drupal site's primary content."),
    'weight' => -10,
  );

The following items are available in the base section :

field

The primary key field for this table. For Views to treat any table as a base table, it must have a primary field. For node this is the 'nid', for users this is the 'uid', etc. Without a single primary key field (i.e. not a composite key), Views will not be able to utilize the table as a base table. If your table does not have a primary key field, it is not too difficult to just add a serial field to it, usually.

title

The title of this table in the UI. It should be singular and describe the object that this table contains from the perspective of the user.

help

A short piece of text to describe what object this table contains.

database

If this table is held in a different database from your Drupal database, specify it as a string in the exact same format as the settings.php file. This is a special purpose variable that will probably be only used in site specific code, and it must be the same database type as your Drupal database. Also, don't try to join it to any table that isn't in the same database. That'll just create all kinds of silly errors. For example:

  // In settings.php for your site
  // Your drupal (site) database needs to be called 'default'
  $db_url['default'] = 'mysqli://user:pass@host/drupal_db';
  $db_url['budget'] = 'mysqli://user:pass@host/other_db';

Then when you are describing the external database in your base table you would write something like this:

  $data[$table]['table']['base'] = array(
    'field' => 'Primary key',
    'title' => t('Field name'),
    'help' => t('Field description'),
    'database' => 'budget',
    'weight' => -10,
    );

'join': Linking your table to existing base tables

For Views to use your table, it has to either be a base table, or know how to link to an existing base table. Or sometimes both. Views uses this information to create a path to the base table; when the table is added to the query, Views will walk along this path, adding all tables required into the query.

How term_data joins to node

In the above example, to use these with 'node' as the base table, both 'term_data' and 'term_node' need to be defined, and they each need a join handler for node:

$data['term_data']['table']['join']['node'] = array(
  'left_table' => 'term_node',
  'left_field' => 'tid',
  'field' => 'tid',
);

The above can be read as "In order to join to the node table, the term_data table must first link to the term_node table, and they join on the 'tid' field.". When adding this table to the query for a node view, Views will look at this and then look for the term_node table.

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

Above, the fact that 'left_table' is left out lets us know that term_node links directly to the node table, using the 'nid' field on both sides of the join.

Quite a few more fields are available in this definition:

handler

The name of the handler object to use. Defaults to 'views_join'. You may create custom join handlers that may or may not use any of the data below, as they see fit.

table

Table to join. This is optional, and should only be used if the table being referenced is an alias.

field

Field to join on. This is required.

left_table

The next step toward the final destination. If this is the final destination it may be omitted.

left_field

The field to join to on the left side. This is required.

type

Either LEFT (default) or INNER.

extra

Either a string that's directly added, or an array of items. Each item is, itself, an array:

field: Field or formula
operator: Similar to filters, this is the operator, such as >, <, =, etc. Defaults to = or IN.
value: Must be set. If an array, operator will be defaulted to IN.
numeric: If true, the value will not be surrounded in quotes, and %d will be used for its placeholder.

extra type

How all the extras will be combined. Either AND or OR. Defaults to AND.

Describing fields on tables

Aside from the special table tag, each table can also have an unlimited number of field designations; these correspond roughly to fields on the table, though it is very common to use non-fields to display data that isn't directly in a field, such as data arrived from formulae, or special links related to the object the table is part of.

Each field is described in the view data with an array, keyed to the database name of the field. This array may contain some information fields, plus an entry in each of the five types of items Views has per field: argument, field, filter, relationship, sort. For example:

$data['node']['nid'] = array(
  'title' => t('Nid'),
  'help' => t('The node ID of the node.'), // The help that appears on the UI,
  // Information for displaying the nid
  'field' => array(
    'handler' => 'views_handler_field_node',
    'click sortable' => TRUE,
  ),
  // Information for accepting a nid as an argument
  'argument' => array(
    'handler' => 'views_handler_argument_node_nid',
    'name field' => 'title', // the field to display in the summary.
    'numeric' => TRUE,
    'validate type' => 'nid',
  ),
  // Information for accepting a nid as a filter
  'filter' => array(
    'handler' => 'views_handler_filter_numeric',
  ),
  // Information for sorting on a nid.
  'sort' => array(
    'handler' => 'views_handler_sort',
  ),
);

The above example describes the 'nid' field on the 'node' table, providing 4 of the 5 handlers. Note that while field is normally expected to be the database name of the field, it doesn't have to be; you can use an alias (which is how you get multiple handlers per field) or something completely made up for items that aren't tied to the database. For example:

$data['node']['edit_node'] = array(
  'field' => array(
    'title' => t('Edit link'),
    'help' => t('Provide a simple link to edit the node.'),
    'handler' => 'views_handler_field_node_link_edit',
  ),
);

The above handler definition an edit link to a node, but this isn't a field in and of itself. For aliased fields, here is another example:

$data['users']['uid_current'] = array(
  'real field' => 'uid',
  'title' => t('Current'),
  'help' => t('Filter the view to the currently logged in user.'),
  'filter' => array(
    'handler' => 'views_handler_filter_user_current',
  ),
);

The above definition provides an alternate filter handler on the uid field for the current user.

The following items are allowed in the field definition:

group, title, help: As above, these fields are for the UI. If placed here, any of these fields will override a setting on the base table.
real field: If this field is an alias, the "real field" may be placed here, and the handler will never know the difference.
field: A handler definition for the "Field" section, which is a field that may be displayed in a view. The definition is an array; the contents of the array are completely up to the handler, other than the 'handler' definition. If omitted, handler will default to 'views_handler_field'.
filter: A handler definition for the "Filters" section, which will be used to apply WHERE clauses to the view. The definition is an array; the contents of the array are completely up to the handler, other than the 'handler' definition. If omitted, handler will default to 'views_handler_filter'.
sort: A handler definition for the "Sort criteria" section, which will be used to add an ORDER BY clause to the view. The definition is an array; the contents of the array are completely up to the handler, other than the 'handler' definition. If omitted, handler will default to 'views_handler_sort'.
relationship: A handler definition for the "Field" section, which is a way to bring in new or alternative base tables in the view. The definition is an array; the contents of the array are completely up to the handler, other than the 'handler' definition. If omitted, handler will default to 'views_handler_relationship'. The basic relationship handler requires 'base' and 'base field' to be set; 'base' and 'base field' represent the "right" half of the join that will use this field as the left side.
argument: A handler definition for the "Field" section, which is method of accepting user input from the URL or some other source. The definition is an array; the contents of the array are completely up to the handler, other than the 'handler' definition. If omitted, handler will default to 'views_handler_argument'.

For more information about what handlers need/use what data, visit the Views API site and check out the available handlers.