0

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.

0

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

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.