Views API. Define custom table

Drupal developers sometimes find themselves trying to handle the tasks that require creating a custom table in the database so as to later interact with this table through your own requests. Inherent to such cases is the requirement that multiple requests be issued, and that can happen to be quite exhausting. All you have to do to avoid the latter inconvenience is to describe the table for community's favorite module Views. Here, in this article, we will take a look at how it can be done.

First of all, we create a new table in the database with the use of hook_schema():

/**
 * Implements hook_schema().
 */
function example_module_schema() {
  $schema['example_table'] = array(
    'fields' => array(
      'nid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => FALSE,
        'default' => 0,
        'description' => 'Node {node}.nid.',
      ),
      'uid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => FALSE,
        'default' => 0,
        'description' => 'User {users}.uid.',
      ),
      'plai_text_field' => array(
        'description' => 'Example textfield.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'timestamp_field' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'description' => 'Some timestamp.',
      ),
    ),
    'primary key' => array('nid'),
  );
  
  return $schema;
}

In the table there should be written down the node's ID, user’s UID, any node/user relevant text fragment (a review, comment) and the timestamp.

Further on, an announcement will be made that the views API will be used in this module:

/**
 * Implements hook_views_api().
 */
function example_module_views_api() {
  return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'example_module'),
  );
}

Then we create a file example_module.views.inc and define hook_views_data() there. The first section contains the table description indicating the following properties:

• "group" - the name of the group in which our setup will be displayed. For example: "Node: Node ID", "Taxonomy: Term description". This preference (characteristic, property) is crucial for the integrity because the user interface views fields as sorted into groups and that facilitates the search.
• "title" - the name of the field: it must be clear and intuitive
• "help" – a more detailed description

Normally, only preference "group" is used.

If you want your table to be central in the views, you can define it as a core one.

In this case, you should specify the following preferences:

• "field" - primary key for the defined table. If you want to define a base table, a primary key is required. Nid is for the table node primary, UID is for users.
• "title" - the table name to be displayed in the user interface
• "help" - a more detailed description
• "database" - in case the defined table resides in some other database this field is obligatory

We can also create an implicit connection between our table and any other (in this example, connection is with the "node" table). To do this, we can specify the following properties in the section "join":

• "handler" - the handler that will be used. The default one is "views_join". You may use your own one, but in the case of the common problems' solution it is not needed.
• "table" - the table we will join. An optional feature which is useless in generalized cases
• "field" - field to join to. This is required
• "left_table" - the next step, on the way toward the table we are aiming at, is the interim table. If this happens to be 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.

/**
 * Implements hook_views_data()
 */
function example_module_views_data() {
  // The 'group' index will be used as a prefix in the UI for any of this
  // table's fields, sort criteria, etc. so it's easy to tell where they came
  // from.
  $data['example_table']['table']['group'] = t('Example table');

  // Define this as a base table. In reality this is not very useful for
  // this table, as it isn't really a distinct object of its own, but
  // it makes a good example.
  $data['example_table']['table']['base'] = array(
    'field' => 'nid',
    'title' => t('Example table'),
    'help' => t("Example table contains example content and can be related to nodes."),
    'weight' => -10,
  );

  // This table references the {node} table.
  // This creates an 'implicit' relationship to the node table, so that when 'Node'
  // is the base table, the fields are automatically available.
  $data['example_table']['table']['join'] = array(
    // Index this array by the table name to which this table refers.
    // 'left_field' is the primary key in the referenced table.
    // 'field' is the foreign key in this table.
    'node' => array(
      'left_field' => 'nid',
      'field' => 'nid',
    ),
  );

Thus, we have the defined a table. We next proceed to the table fields description. The fields can have the following properties:

• "group", "title", "help" – for the table, the values of these fields are used in the user interface "real field" - if the field is synonymous with it, you need to specify the actual name of the field here
• "field" - array defining of the handler for the section "Field". "views_handler_field" is used by default. If this option is specified, then the field will appear in the list of fields to display. The attribute "click_sortable", installed in TRUE, enables sorting by that field (in the tables for example)
• "filter" – the array defining of the handler for the section "Filters". "views_handler_filter" is used for the default. If this option is specified, then the field will appear in the list of fields to filter
• "sort" – the array defining of the handler for the section "Sort criteria". "views_handler_sort" is used for the default. If this option is specified, then the field will appear in the list of fields to sort "relationship" - enables you to add connections in this field - section
• "relationship". "views_handler_relationship" is used for the default. It is essential to specify the base table to join - element "base" and the field (an element "field") this connection will be in
• "argument" - array defining a handler for the section "Contextual Filters". The handler "views_handler_argument" is used for the default

 // Next, describe each of the individual fields in this table to Views. For
  // each field, you may 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']['nid'] = array(
    'title' => t('Example content'),
    'help' => t('Some example content that references a node.'),
    // Because this is a foreign key to the {node} table. This allows us to
    // have, when the view is configured with this relationship, all the fields
    // for the related node available.
    'relationship' => array(
      'base' => 'node',
      'field' => 'nid',
      'handler' => 'views_handler_relationship',
      'label' => t('Example node'),
    ),
  );

  // Example plain text field.
  $data['example_table']['plain_text_field'] = array(
    'title' => t('Plain text field'),
    'help' => t('Just a plain text field.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );


  // Example timestamp field.
  $data['example_table']['timestamp_field'] = array(
    'title' => t('Timestamp field'),
    'help' => t('Just a timestamp field.'),
    'field' => array(
      'handler' => 'views_handler_field_date',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort_date',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_date',
    ),
  );

  return $data;
}

In the end of the day we have a user-friendly interface, skip the necessity to issue millions of requests and the site maintenance appears to be simple and easy.

Internerdevels website development company appreciates your time.