Add foreign key to schema using hook_update_N

Question

I've written a module that establishes a child/parent relation ship between magazine issues and the articles in each issue. The idea is that by associating articles with magazine issues they can all be automatically published on a given date via cron. That all works fine.

The problem is I'm trying to use a View to list a subset of those articles and list them according the parent issues date, but that isn't showing up as a relationship. I assume the problem is that I haven't defined a foreign key in my schema.

Is it possible to add one in hook_update_N or whatever?

To clarify, the relationship is created using fields defined in a custom module using the Field API where the parent node has the date info and the child node has a field for the parent NID.

Answer 1

You can add keys to tables using native SQL. It's not supported by drupal 7 core api. I answered this previously elsewhere: Is there a way to add a foreign key to a table?

I doubt that simply adding a key will expose the relationship for you in Views though.

In order for Views to see this information you likely need to supply the info via hook_views_data() about your custom tables and relationships. A recent blog post I found about this today actually was: https://chacadwa.com/blog/2014/08/12/adding-full-views-support-custom-entities

From this blog post you can see that for a table that needs a join to add extra fields (and to do so by default in Views) they add the following array to their hook_views_data() function.

// Define a default relationship to the staffdir extra data table.

$data['hank_hrper']['table']['default_relationship'] = array(
  'hank_hrper_extra_data' => array(
    'table' => 'hank_hrper_extra_data',
    'field' => 'hrper_id',
  ),
);

// Now define the staffdir extra data table, so views can use it.

$data['hank_hrper_extra_data'] = array(
  'table' => array(
    'group' => t('Staff directory'),
    'entity type' => 'staffdir',
    'join' => array(
      'hank_hrper' => array(
        'left_field' => 'hrper_id',
        'field' => 'hrper_id',
      ),
    ),
    'default_relationship' => array(
      'hank_hrper' => array(
        'table' => 'hank_hrper',
        'field' => 'hrper_id',
      ),
    ),
  ),
);

// Now we can define fields from the staffdir extra data table for use in the relationship.

$data['hank_hrper_extra_data']['opt_out'] = array(
  'title' => t('Opt out'),
  'help' => t('Allows for users to be excluded from the directory.'),
  'field' => array(
    'handler' => 'views_handler_field_boolean',
    'click sortable' => FALSE, // could be TRUE, but why?
  ),
  'filter' => array(
    'handler' => 'views_handler_filter_boolean_operator',
     'accept null' => TRUE,
    'help' => t('Filter results to a particular result set'),
  ),
);

Answer 2

MySQL foreign keys and Views relationships are separate, unrelated concepts - to tell Views about a relationship between two fields, you need to use hook_views_data() (or hook_views_data_alter() if you're altering another module's data).

You can define foreign keys in hook_schema(), and subsequently update them using db_change_field() in an update hook, but it won't do anything.

Foreign keys are defined for documentation purposes only in Drupal, there's nothing enforced at the DBMS level.

Answer 3

In the end it turned out to be pretty easy to establish the relationship using some code stolen from the excellent references module (specifically node_reference). I made that a dependency of my module so I could re-use their view handler 'references_handler_relationship'. For completeness I'd have extracted that and placed similar code in my own module.

Incidentally that is also where I got the foreign key idea, which will apparently be more relevant in later versions of Drupal and currently only in use as documentation.

<?php
/**
 * Implements hook_field_views_data().
 *
 * In addition to the default field information we add the relationship for
 * views to connect back to the node table.
 */
function pubdate_field_views_data($field) {
  // No module_load_include(): this hook is invoked from
  // views/modules/field.views.inc, which is where that function is defined.
  $data = field_views_field_default_views_data($field);

  $storage = $field['storage']['details']['sql'];
  foreach ($storage as $age => $table_data) {
    $table = key($table_data);
    $columns = current($table_data);
    $id_column = $columns['pubdate_parent'];
    if ($field['type'] == 'pubdate_parent' && isset($data[$table])) {
      // Relationship.
      $data[$table][$id_column]['relationship'] = array(
        'handler' => 'references_handler_relationship',
        'base' => 'node',
        'base field' => 'nid',
        'field' => $id_column,
        'label' => $field['field_name'],
        'field_name' => $field['field_name'],
      );
    }
  }

  return $data;
}