Views hook_views_data add multiple joins to custom table based on row condition

Question

I'm using hook_views_data to add a custom table and its fields to Views. The table relates to nodes, and looks like this:

+ node id   | my_value   | time_period  +
|-----------|------------|--------------|
| 1         | 100        | 1 year       |
| 1         | 200        | 1 month      |
| 2         | 300        | 1 year       |
| 2         | 400        | 1 month      |
+---------------------------------------+

I have defined an implicit relationship to my custom table, so I have a view with nodes as the base table and I can access the my_value field from my custom table.

The time_period column is new, however. Now there are multiple rows per node, with different time_period values, and each of these results in a different output row in my view.

Rather than having a my_value field for each row, I would like to have my_value (1 year) and my_value (1 month) fields, so that each output row represents one node - i.e. rows for the same node are collapsed and their fields are separated.

I've cobbled together the SQL that achieves this, but I can't figure out how to accomplish this with hook_views_data:

SELECT 
    node.title AS node_title, 
    node.nid AS nid, 
    custom_table_1month.my_value AS my_value_1month,
    custom_table_1year.views AS my_value_1year
FROM 
    node 
LEFT JOIN
    custom_table custom_table_1month ON custom_table_1month.node_id = node.nid AND custom_table_1month.time_period = '1 month'
LEFT JOIN
    custom_table custom_table_1year ON custom_table_1year.node_id = node.nid AND custom_table_1year.time_period = '1 year'

I have tried to add two base tables to views, called custom_table_1month and custom_table_1year, but these value names are used verbatim as the table name whereas I really want them as aliases, so Views complains that these tables don't exist, and I can't seem to specify the correct table name anywhere.

I'm feeling like this has to be possible, because I've got the SQL for it! I just can't for the life of me figure out how to do this with Views.

Answer 1

You can try code

file my_module.views.inc

/**
     * Implements hook_views_data().
     */
    function my_module_views_data() {
      $data['custom']['views_handler_custom_field'] = array(
        'title' => t('Custom views field'),
        'help' => t('Field custom handler'),
        'field' => array(
          'handler' => 'views_handler_custom_field',
        ),
      );
    }

file my_module.module

/**
 * Implements hook_views_api().
 */
function my_module_views_api() {
  return array(
    'api' => 3
  );
}

file views_handler_custom_field.inc

class views_handler_custom_field extends views_handler_field{
  function query() {

    $this->ensure_my_table();
    $alias = 'custom_table';
    $this->query->add_field($alias,'node_id');

    // Month, you can copy it
    $aliasNode = 'node';
    $definition = array(
      'table' => $aliasNode,
      'field' => 'tid',
      'left_table' => 'custom_table',
      'left_field' => 'node_id',
      'type' => 'LEFT OUTER',
    );
    $join = new views_join();
    $join->definition = $definition;
    $join->construct();
    $join->adjusted = TRUE;
    $this->query->add_relationship($aliasNode, $join,$this->relationship);
    $formula ="";
    $this->query->add_field(NULL,$formula,'node_id');

  }

  function option_definition() {
    $options = parent::option_definition();
    $options['alter']['contains']['alter_text'] = array('default' => TRUE);
    return $options;
  }

  function options_form(&$form, &$form_state) {
    parent::options_form($form, $form_state);
  }

  function render($values) {
    var_dump($values);
  }
}

The API