How to integrate custom tables with Views in Drupal 6?

| | 2 min read

While trying to fix an issue with Drupal Views, I wanted to use a single custom table to store the char count of the body field of different content types instead of creating separate CCK fields for each content type to store the same. The question that came to my mind was how to integrate my custom database table with Drupal Views? Drupal's Views module provides a hook for doing this - hook_views_data().

This hook should be included in ourmodulename.views.inc file, which must either be in the same directory as the .module file or in a subdirectory named 'includes'. This will be automatically loaded.

An example which I used is given below.

function ourmodulename_views_data() {
  // The 'group' index will be used as a prefix in the UI for any of this
  // table's fields, sort criteria, etc.
  $data['example_table_name']['table']['group'] = t('Node word/char count table');
  // Define this as a base table. 
  $data['example_table_name']['table']['base'] = array(
    'field' => 'g_news_id',
    'title' => t('example_table_name table'),
    'help' => t("Table contains node word count and length"),
    'weight' => -10,
  );
  // This table creates an 'implicit' relationship to the node table, so that when 'Node'
  // is the base table, the fields are automatically available 
  $data['example_table_name']['table']['join'] = array(
    'node' => array(
      'left_field' => 'nid',
      'field' => 'nid',
     ),
  );
  // Now, explain all the fields in this table. For
  // each field, you can define what field, sort, argument, and/or filter
  // handlers it supports. This will determine where in the Views interface you
  // may use the field.
  // Node ID field.
  $data['example_table_name']['nid'] = array(
    'title' => t('Node content'),
    'relationship' => array(
      'base' => 'node',
      'field' => 'nid',
      'handler' => 'views_handler_relationship',
      'label' => t('Node id in this table that references a node.'),
    ),
  );
  // Explain numeric fields in the table
  $data['example_table_name']['chars'] = array(
    'title' => t('Character count'),
    'help' => t('Just a numeric field.'),
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );
  $data['example_table_name']['chars_html_stripped'] = array(
    'title' => t('Character count w/o html'),
    'help' => t('Just a numeric field.'),
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );
  $data['example_table_name']['words_html_stripped'] = array(
    'title' => t('Word count '),
    'help' => t('Just a numeric field.'),
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );
  return $data;
}