Relate data in a custom sql table to a regular drupal field using views

Question

I have a field collection, with a field "Attribute number", which contains a text value of the format "b1_a1", "b7_c2", etc.

I have a custom table that has a column that references an attribute number, and a column that contains a survey response value (yes or no).

I want to create a relationship between the attribute number field and my table, so that in views, I can list the attribute numbers, and display the corresponding response.

I'm looking at hook_data_alter(). There are example to extend a base table, such as node, and join with a new table, but there is no example to extend a non base table, such as my field_data_field_attribute_num.

How do I make a views join with a non base table?

Answer 1

I assume you will be associating your "Attribute number" field to a node type, and that your view will be showing nodes. So, what you need to do here is describe your custom table (I call it "custom_an" here) to the views API and provide a way to join it when displaying node data. Here is a way to do that:

function YOURMODULE_views_data() {
  // Assign a group for the custom table:
  // can be an existing group - e.g. t("Content") - 
  // or, like I  do here, a new custom group.
  $data['custom_an']['table']['group'] = t('Extended content'); 


  $data['custom_an']['table']['join'] = array(
    // This table will be available for join for any 'node' view.
    // 'left_table' is the referenced table, 
    // i.e. the one that contains your field's data
    // 'left_field' is the join key in the referenced table.
    // 'field' is the foreign key in the custom table.
    'node' => array(
      'left_table' => 'field_data_field_attribute_number',
      'left_field' => 'field_attribute_number_value',
      'field' => 'attribute_number',
    ),
  );

  // here we describe the field(s) in the custom table
  // that we want to show in the view.
  $data['custom_an']['response'] = array(
    'title' => t('Response'),
    'help' => t('Response field.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE, // This is use by the table display plugin.
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );

  return $data;
}

Please be aware that this is only a proof of concept, and that it's meant to get you started but in no way you should use this code as is for a production site. Two major issues to be addressed would be:

• testing this code against a node type which does not contain the "Attribute number" field (and possibly find the right way to avoid that adding the custom data to that view results in a corrupted view), and
• if attribute_number in your custom table is not unique, the view could output duplicate rows. The join key in the custom table should actually be a primary key, according to hook_views_data() documentation.