1. 6.x-3.x includes/handlers.inc views_join
  2. 7.x-3.x includes/handlers.inc views_join

A function class to represent a join and create the SQL necessary to implement the join.

This is the Delegation pattern. If we had PHP5 exclusively, we would declare this an interface.

Extensions of this class can be used to create more interesting joins.

join definition

  • table: table to join (right table)
  • field: field to join on (right field)
  • left_table: The table we join to
  • left_field: The field we join to
  • type: either LEFT (default) or INNER
  • extra: An array of extra conditions on the join. Each condition is either a string that's directly added, or an array of items:
  • - table: If not set, current table; if NULL, no table. If you specify a table in cached definition, Views will try to load from an existing alias. If you use realtime joins, it works better.
  • - field: Field or formula in formulas we can reference the right table by using %alias

    • - operator: defaults to =
  • - value: Must be set. If an array, operator will be defaulted to IN.
  • - numeric: If true, the value will not be surrounded in quotes.
  • - extra type: How all the extras will be combined. Either AND or OR. Defaults to AND.

Hierarchy

Expanded class hierarchy of views_join

See also

SelectQueryInterface::addJoin()

Related topics

File

includes/handlers.inc, line 1473
Defines the various handler objects to help build and display views.

View source
class views_join {
  var $table = NULL;
  var $left_table = NULL;
  var $left_field = NULL;
  var $field = NULL;
  var $extra = NULL;
  var $type = NULL;
  var $definition = array(
    
  );
  
  /**
   * Construct the views_join object.
   */
  function construct($table = NULL, $left_table = NULL, $left_field = NULL, $field = NULL, $extra = array(
    
  ), $type = 'LEFT') {
    $this->extra_type = 'AND';
    if (!empty($table)) {
      $this->table = $table;
      $this->left_table = $left_table;
      $this->left_field = $left_field;
      $this->field = $field;
      $this->extra = $extra;
      $this->type = strtoupper($type);
    }
    elseif (!empty($this->definition)) {
      
      // if no arguments, construct from definition.
      // These four must exist or it will throw notices.
      $this->table = $this->definition['table'];
      $this->left_table = $this->definition['left_table'];
      $this->left_field = $this->definition['left_field'];
      $this->field = $this->definition['field'];
      if (!empty($this->definition['extra'])) {
        $this->extra = $this->definition['extra'];
      }
      if (!empty($this->definition['extra type'])) {
        $this->extra_type = strtoupper($this->definition['extra type']);
      }
      $this->type = !empty($this->definition['type']) ? strtoupper($this->definition['type']) : 'LEFT';
    }
  }
  
  /**
   * Build the SQL for the join this object represents.
   *
   * When possible, try to use table alias instead of table names.
   *
   * @param $select_query
   *   An implementation of SelectQueryInterface.
   * @param $table
   *   The base table to join.
   * @param $view_query
   *   The source query, implementation of views_plugin_query.
   */
  function build_join($select_query, $table, $view_query) {
    if (empty($this->definition['table formula'])) {
      $right_table = $this->table;
    }
    else {
      $right_table = $this->definition['table formula'];
    }
    if ($this->left_table) {
      $left = $view_query->get_table_info($this->left_table);
      $left_field = "{<span class="php-variable">$left</span>[<span class="php-string">'alias'</span>]}.{<span class="php-variable">$this</span>-&gt;<span class="php-function-or-constant property member-of-self">left_field</span>}";
    }
    else {
      
      // This can be used if left_field is a formula or something. It should be used only *very* rarely.
      $left_field = $this->left_field;
    }
    $condition = "{<span class="php-variable">$left_field</span>} = {<span class="php-variable">$table</span>[<span class="php-string">'alias'</span>]}.{<span class="php-variable">$this</span>-&gt;<span class="php-function-or-constant property member-of-self">field</span>}";
    $arguments = array(
      
    );
    
    // Tack on the extra.
    if (isset($this->extra)) {
      
      // If extra has been provided as string instead of an array, convert it
      // to an array.
      if (!is_array($this->extra)) {
        $this->extra = array(
          $this->extra,
        );
      }
      $extras = array(
        
      );
      foreach ($this->extra as $info) {
        if (is_array($info)) {
          $extra = '';
          
          // Figure out the table name. Remember, only use aliases provided
          // if at all possible.
          $join_table = '';
          if (!array_key_exists('table', $info)) {
            $join_table = $table['alias'] . '.';
          }
          elseif (isset($info['table'])) {
            
            // If we're aware of a table alias for this table, use the table
            // alias instead of the table name.
            if (isset($left) && $left['table'] == $info['table']) {
              $join_table = $left['alias'] . '.';
            }
            else {
              $join_table = $info['table'] . '.';
            }
          }
          
          // Convert a single-valued array of values to the single-value case,
          // and transform from IN() notation to = notation
          if (is_array($info['value']) && count($info['value']) == 1) {
            if (empty($info['operator'])) {
              $operator = '=';
            }
            else {
              $operator = $info['operator'] == 'NOT IN' ? '!=' : '=';
            }
            $info['value'] = array_shift($info['value']);
          }
          if (is_array($info['value'])) {
            
            // With an array of values, we need multiple placeholders and the
            // 'IN' operator is implicit.
            foreach ($info['value'] as $value) {
              $placeholder_i = $view_query->placeholder('views_join_condition_');
              $arguments[$placeholder_i] = $value;
            }
            $operator = !empty($info['operator']) ? $info['operator'] : 'IN';
            $placeholder = '( ' . implode(', ', array_keys($arguments)) . ' )';
          }
          else {
            
            // With a single value, the '=' operator is implicit.
            $operator = !empty($info['operator']) ? $info['operator'] : '=';
            $placeholder = $view_query->placeholder('views_join_condition_');
            $arguments[$placeholder] = $info['value'];
          }
          $extras[] = "{<span class="php-variable">$join_table</span>}{<span class="php-variable">$info</span>[<span class="php-string">'field'</span>]} {<span class="php-variable">$operator</span>} {<span class="php-variable">$placeholder</span>}";
        }
        elseif (is_string($info)) {
          $extras[] = $info;
        }
      }
      if ($extras) {
        if (count($extras) == 1) {
          $condition .= ' AND ' . array_shift($extras);
        }
        else {
          $condition .= ' AND (' . implode(' ' . $this->extra_type . ' ', $extras) . ')';
        }
      }
    }
    $select_query->addJoin($this->type, $right_table, $table['alias'], $condition, $arguments);
  }

}

Members

Contains filters are case sensitive
Namesort descending Modifiers Type Description Overrides
views_join::$definition property
views_join::$extra property
views_join::$field property
views_join::$left_field property
views_join::$left_table property
views_join::$table property
views_join::$type property
views_join::build_join function Build the SQL for the join this object represents. 1
views_join::construct function Construct the views_join object. 1

Comments

dahousecat’s picture

The structure of the extra array confused me for a bit.

This is what it needs to look like:

function hook_views_query_alter(&$view, &$query) {
	$join = new views_join();
	$join->table = 'my_table';
	$join->field = 'my_field';
	$join->left_table = 'left_table';
	$join->left_field = 'left_field';
	$join->type = 'left';
	$join->extra = array(
		array(
			'field' => 'bundle',
			'value' => 'user',
		)
	);
	$query->add_relationship('relationship_name', $join, 'node');
}
phponwebsites’s picture

I used below codes to alter views query.

function hook_views_query_alter(&$view, &$query) {
    $join = new views_join();
    $join->table = 'my_table';
    $join->field = 'my_field';
    $join->left_table = 'left_table';
    $join->left_field = 'left_field';
    $join->type = 'left';
    $join->extra = array(
        array(
            'field' => 'bundle',
            'value' => 'user',
        )
    );
    $query->add_relationship('relationship_name', $join, 'node');
}

Now i want to add one more field in $join->extra How to do this?

sinasalek’s picture

How to write a subquery join?! easy...

    $sub_query = db_select('workflow_node_history', 'sub_workflow_node_history_states');
    $sub_query->addField('sub_workflow_node_history_states', 'nid');
    $sub_query->addField('sub_workflow_states', 'weight', 'weight');
    $sub_query->addField('sub_workflow_states_old', 'weight' , 'old_weight');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states', 'sub_workflow_node_history_states.sid = sub_workflow_states.sid');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states_old', 'sub_workflow_node_history_states.old_sid = sub_workflow_states_old.sid');
    $sub_query->orderBy('sub_workflow_node_history_states.hid', 'DESC');

    $join = new views_join();
    $join->definition = array('table formula' => $sub_query, 'left_field' => 'nid', 'field' => 'nid', 'left_table' => 'node');
    $join->left_table = 'node';// or wathever table you want to join with
    $join->field = 'nid';
    $join->left_field = 'nid';// or wathever column you want to join with
    $join->type = 'LEFT';// if you want another join type play with this value

    // Do the actual join
    $this->query->table_queue['workflow_node_history_states'] = array (
      'alias' => 'workflow_node_history_states',// I believe this is optional
      'table' => $sub_query,
      'relationship' => 'node',
      'join' => $join,
    );
infinet’s picture

That was really helpful... took a while to find a good solution for this.

infinet’s picture

I ended up using this technique to create a views_handler_field for a Drupal Commerce View that lists products. The custom field displays the number of times each product is included in any active carts.

You can see the full solution here http://divingintodrupal8.com/#joining-a-views-query-to-a-derived-table-o...

id.iot’s picture

If you have the need for complex join conditions, you can define the extra parameter like so:

$join = new views_join();
...
$join->extra = "table_1.field_1 = value_1 AND (table_2.field_2 = value_2 OR table_2.field_3 IS NOT NULL)";
RyanPrice’s picture

This is how I was able to add a join successfully:

<?php
  // Write the join conditions
  $join = new views_join();
  $join->table = 'field_data_field_blog_title';
  $join->left_table = 'blog';// or wathever table you want to join with
  $join->field = 'id';
  $join->left_field = 'id';// or wathever column you want to join with
  $join->type = 'LEFT';// if you want another join type play with this value

  // Do the actual join
  $query->table_queue['blog_title'] = array (
    'alias' => 'blog_title',// I believe this is optional
    'table' => 'field_data_field_blog_title',
    'relationship' => 'blog',
    'join' => $join,
  );
  $query->where[] = array(
    'conditions' => array(
      array(
        'field' => 'blog_title.language',
        'value' => $language,
        'operator' => '=',
      ),
      // add more conditions if you want to
    ),
    //'type' => 'OR' ,// I believe this is an operator for multiple conditions
  );
?>

Credit to absoludo for this, taken from: https://www.drupal.org/node/2049051

anumathew’s picture

This worked for me

mchar’s picture

For those who tried the above code and got this error


Notice: Undefined index: type in views_plugin_query_default->build_condition() (line 1130 of E:\drupal.projects\edn.dev\website\sites\all\modules\contrib\views\plugins\views_plugin_query_default.inc).

simply add those two lines of code at your query construction.


'args' => array(),
'type' => 'AND',

mjsilverman’s picture

Here's the code I used to add a JOIN to the taxonomy index table when passing it directly to the view was not an option.

$join_obj = new views_join();
$join_obj->table = 'taxonomy_index';
$join_obj->left_table = 'node';
$join_obj->left_field = 'nid';
$join_obj->field = 'nid';
$join_obj->extra[] = array(
'field'=>'tid',
'value' => $tid,
'numeric'=> TRUE,
);
$join_obj->type = 'INNER';
$join_obj->definition = array(
'left_field'=>'nid',
'field'=>'nid',
'table'=>'taxonomy_index',
'left_table'=>'node',
);
$join_obj->extra_type = 'AND';

$query->table_queue['taxonomy_index_value_0'] = array(
'table' => "taxonomy_index",
'num' => 1,
'alias' => 'taxonomy_index_value_0',
'join' => $join_obj,
'relationship' => 'node',
);

generalredneck’s picture

Just to be clear (as it wasn't obvious to me looking at this page), as shown by RyanPrice above, Operator goes on the same level as field, value, and numeric. There is a bit of funkiness with the way the doc block was parsed to make this page. This is apparently when you look at the source includes/handlers.inc,

gslexie’s picture

Likewise, the documentation seems to be off for 'extra type'. This is on the top level, and the property of the join object is called 'extra_type'. Use like: $join_object->extra_type.

If populating the object directly, instead of using the constructor, the documented default value of 'AND' will not work; extra_type must be populated manually if there are multiple conditions, else there will be query errors.