0

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.

  • How did you make these magazines and articles? Are these drupal content types? Taxonomy references? Did you add a reference from Articles to the parent Magazine? Or are these custom tables you've added to your database? – tenken Apr 27 '15 at 15:40
2

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'),
  ),
);
1

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.

0

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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.