Views : custom table with relationship is not queried

Question

I use a custom table to store data on my nodes, and hook on _views_data to display or sort from this table.

Everything's going fine :

/**
* Implements hook_views_data()
*/
function MYMODULE_views_data() {

  $data['MYTABLE']['table']['join'] = array(
    'node' => array(
      'left_field' => 'nid',
      'field' => 'nid',
    )
  );
  $data['MYTABLE']['MYFIELD'] = array(
    'field' => array(
      'handler' => 'MYHANDLER',
    )
  );
}

class MYCLASS extends views_handler_field {
  function query() {
    // Add 2 columns I need to the query
    $this->view->query->add_field('MYTABLE', 'COLUMN1', 'ALIAS1');
    $this->view->query->add_field('MYTABLE', 'COLUMN2', 'ALIAS2');
  }

function render($values) {
    // WORKS FINE WITH NO RELATIONSHIP
    $dates = array($values->ALIAS1, $values->ALIAS2);
    ...
    return ..
  }
}

My problem is : I can't get values when my table is called on a node-relationship relationship.

I tried

function query() {
        if(isset($this->relationship) && !empty($this->relationship)) {
            $table = $this->view->relationship[$this->options['relationship']]->table_alias;
            $field = $this->view->relationship[$this->options['relationship']]->real_field;
            $this->view->query->add_field($table . '.' . $field, 'COLUMN1', 'ALIAS1');
            $this->view->query->add_field($table . '.' . $field, 'COLUMN2', 'ALIAS2');
        }
}

and

function query() {
if (!empty($this->relationship)) {
            $this->table = $this->query->ensure_table($this->table, $this->relationship);
        }

but in render, my $values is always empty when the relationship is set.

Answer 1

Instead of adding the fields manually, you should populate the instance variable additional_fields. From the views documentation:

additional fields: An array of fields that should be added to the query for some purpose. The array is in the form of: array('identifier' => array('table' => tablename, 'field' => fieldname);

Looking through some of the implementations out there indicates that your code should look something like this:

class MYCLASS extends views_handler_field {
  function construct() {
    parent::construct();

    $this->additional_fields['ALIAS1'] = array(
      'field' => 'COLUMN1',
      'table' => 'MYTABLE',
    );
    $this->additional_fields['ALIAS2'] = array(
      'field' => 'COLUMN2',
      'table' => 'MYTABLE',
    );
  }

  function query() {
    $this->ensure_my_table();
    $this->add_additional_fields();
  }

  function render($values) {
    $alias1 = $this->get_value($values, 'ALIAS1');
    $alias2 = $this->get_value($values, 'ALIAS2');
    ...
  }
}