Views hook_views_data add multiple joins to custom table based on row condition 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.
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' =&gt; t('Custom views field'),
        'help' =&gt; t('Field custom handler'),
        'field' =&gt; array(
          'handler' =&gt; 'views_handler_custom_field',
        ),
      );
    }


file my_module.module

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


file views_handler_custom_field.inc

class views_handler_custom_field extends views_handler_field{
  function query() {

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

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

  }

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

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

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


The API