3 namespace Drupal\views\Plugin\views\join;
5 use Drupal\Core\Database\Query\SelectInterface;
6 use Drupal\Core\Plugin\PluginBase;
9 * @defgroup views_join_handlers Views join handler plugins
11 * Handler plugins for Views table joins.
13 * Handler plugins help build the view query object. Join handler plugins
16 * Views join handlers extend \Drupal\views\Plugin\views\join\JoinPluginBase.
17 * They must be annotated with \Drupal\views\Annotation\ViewsJoin annotation,
18 * and they must be in namespace directory Plugin\views\join.
20 * Here are some examples of configuration for the join plugins.
24 * LEFT JOIN {two} ON one.field_a = two.field_b
26 * Use this configuration:
28 * $configuration = array(
30 * 'field' => 'field_b',
31 * 'left_table' => 'one',
32 * 'left_field' => 'field_a',
35 * $join = Views::pluginManager('join')->createInstance('standard', $configuration);
37 * Note that the default join type is a LEFT join when 'type' is not supplied in
38 * the join plugin configuration.
42 * INNER JOIN {two} ON one.field_a = two.field_b AND one.field_c = 'some_val'
44 * Use this configuration:
46 * $configuration = array(
49 * 'field' => 'field_b',
50 * 'left_table' => 'one',
51 * 'left_field' => 'field_a',
55 * 'left_field' => 'field_c',
56 * 'value' => 'some_val',
60 * $join = Views::pluginManager('join')->createInstance('standard', $configuration);
65 * INNER JOIN {two} ON one.field_a = two.field_b AND two.field_d = 'other_val'
67 * Use this configuration:
69 * $configuration = array(
72 * 'field' => 'field_b',
73 * 'left_table' => 'one',
74 * 'left_field' => 'field_a',
78 * 'field' => 'field_d',
79 * 'value' => 'other_val',
83 * $join = Views::pluginManager('join')->createInstance('standard', $configuration);
88 * INNER JOIN {two} ON one.field_a = two.field_b AND one.field_c = two.field_d
90 * Use this configuration:
92 * $configuration = array(
95 * 'field' => 'field_b',
96 * 'left_table' => 'one',
97 * 'left_field' => 'field_a',
101 * 'left_field' => 'field_c',
102 * 'field' => 'field_d',
106 * $join = Views::pluginManager('join')->createInstance('standard', $configuration);
109 * Here is an example of a more complex join:
111 * class JoinComplex extends JoinPluginBase {
112 * public function buildJoin($select_query, $table, $view_query) {
113 * // Add an additional hardcoded condition to the query.
114 * $this->extra = 'foo.bar = baz.boing';
115 * parent::buildJoin($select_query, $table, $view_query);
120 * @ingroup views_plugins
125 * Represents a join and creates the SQL necessary to implement the join.
127 * Extensions of this class can be used to create more interesting joins.
129 class JoinPluginBase extends PluginBase implements JoinPluginInterface {
132 * The table to join (right table).
139 * The field to join on (right field).
146 * The table we join to.
153 * The field we join to.
160 * An array of extra conditions on the join.
162 * Each condition is either a string that's directly added, or an array of
164 * - table(optional): If not set, current table; if NULL, no table. If you
165 * specify a table in cached configuration, Views will try to load from an
166 * existing alias. If you use realtime joins, it works better.
167 * - field(optional): Field or formula. In formulas we can reference the
168 * right table by using %alias.
169 * - left_field(optional): Field or formula. In formulas we can reference
170 * the left table by using %alias.
171 * - operator(optional): The operator used, Defaults to "=".
172 * - value: Must be set. If an array, operator will be defaulted to IN.
173 * - numeric: If true, the value will not be surrounded in quotes.
175 * @see SelectQueryInterface::addJoin()
182 * The join type, so for example LEFT (default) or INNER.
189 * The configuration array passed by initJoin.
193 * @see \Drupal\views\Plugin\views\join\JoinPluginBase::initJoin()
195 public $configuration = [];
198 * How all the extras will be combined. Either AND or OR.
202 public $extraOperator;
205 * Defines whether a join has been adjusted.
207 * Views updates the join object to set the table alias instead of the table
208 * name. Once views has changed the alias it sets the adjusted value so it
209 * does not have to be updated anymore. If you create your own join object
210 * you should set the adjusted in the definition array to TRUE if you already
211 * know the table alias.
215 * @see \Drupal\views\Plugin\HandlerBase::getTableJoin()
216 * @see \Drupal\views\Plugin\views\query\Sql::adjustJoin()
217 * @see \Drupal\views\Plugin\views\relationship\RelationshipPluginBase::query()
222 * Constructs a Drupal\views\Plugin\views\join\JoinPluginBase object.
224 public function __construct(array $configuration, $plugin_id, $plugin_definition) {
225 parent::__construct($configuration, $plugin_id, $plugin_definition);
226 // Merge in some default values.
229 'extra_operator' => 'AND'
231 $this->configuration = $configuration;
233 if (!empty($configuration['table'])) {
234 $this->table = $configuration['table'];
237 $this->leftTable = $configuration['left_table'];
238 $this->leftField = $configuration['left_field'];
239 $this->field = $configuration['field'];
241 if (!empty($configuration['extra'])) {
242 $this->extra = $configuration['extra'];
245 if (isset($configuration['adjusted'])) {
246 $this->adjusted = $configuration['adjusted'];
249 $this->extraOperator = strtoupper($configuration['extra_operator']);
250 $this->type = $configuration['type'];
256 public function buildJoin($select_query, $table, $view_query) {
257 if (empty($this->configuration['table formula'])) {
258 $right_table = $this->table;
261 $right_table = $this->configuration['table formula'];
264 if ($this->leftTable) {
265 $left_table = $view_query->getTableInfo($this->leftTable);
266 $left_field = "$left_table[alias].$this->leftField";
269 // This can be used if left_field is a formula or something. It should be used only *very* rarely.
270 $left_field = $this->leftField;
274 $condition = "$left_field = $table[alias].$this->field";
277 // Tack on the extra.
278 if (isset($this->extra)) {
279 $this->joinAddExtra($arguments, $condition, $table, $select_query, $left_table);
282 $select_query->addJoin($this->type, $right_table, $table['alias'], $condition, $arguments);
285 * Adds the extras to the join condition.
287 * @param array $arguments
288 * Array of query arguments.
289 * @param string $condition
290 * The condition to be built.
291 * @param array $table
293 * @param \Drupal\Core\Database\Query\SelectInterface $select_query
294 * The current select query being built.
295 * @param array $left_table
298 protected function joinAddExtra(&$arguments, &$condition, $table, SelectInterface $select_query, $left_table = NULL) {
299 if (is_array($this->extra)) {
301 foreach ($this->extra as $info) {
302 $extras[] = $this->buildExtra($info, $arguments, $table, $select_query, $left_table);
306 if (count($extras) == 1) {
307 $condition .= ' AND ' . array_shift($extras);
310 $condition .= ' AND (' . implode(' ' . $this->extraOperator . ' ', $extras) . ')';
314 elseif ($this->extra && is_string($this->extra)) {
315 $condition .= " AND ($this->extra)";
320 * Builds a single extra condition.
323 * The extra information. See JoinPluginBase::$extra for details.
324 * @param array $arguments
325 * Array of query arguments.
326 * @param array $table
328 * @param \Drupal\Core\Database\Query\SelectInterface $select_query
329 * The current select query being built.
334 * The extra condition
336 protected function buildExtra($info, &$arguments, $table, SelectInterface $select_query, $left) {
337 // Do not require 'value' to be set; allow for field syntax instead.
341 // Figure out the table name. Remember, only use aliases provided
342 // if at all possible.
344 if (!array_key_exists('table', $info)) {
345 $join_table = $table['alias'] . '.';
347 elseif (isset($info['table'])) {
348 // If we're aware of a table alias for this table, use the table
349 // alias instead of the table name.
350 if (isset($left) && $left['table'] == $info['table']) {
351 $join_table = $left['alias'] . '.';
354 $join_table = $info['table'] . '.';
358 // Convert a single-valued array of values to the single-value case,
359 // and transform from IN() notation to = notation
360 if (is_array($info['value']) && count($info['value']) == 1) {
361 $info['value'] = array_shift($info['value']);
363 if (is_array($info['value'])) {
364 // We use an SA-CORE-2014-005 conformant placeholder for our array
365 // of values. Also, note that the 'IN' operator is implicit.
366 // @see https://www.drupal.org/node/2401615.
367 $operator = !empty($info['operator']) ? $info['operator'] : 'IN';
368 $placeholder = ':views_join_condition_' . $select_query->nextPlaceholder() . '[]';
369 $placeholder_sql = "( $placeholder )";
372 // With a single value, the '=' operator is implicit.
373 $operator = !empty($info['operator']) ? $info['operator'] : '=';
374 $placeholder = $placeholder_sql = ':views_join_condition_' . $select_query->nextPlaceholder();
376 // Set 'field' as join table field if available or set 'left field' as
377 // join table field is not set.
378 if (isset($info['field'])) {
379 $join_table_field = "$join_table$info[field]";
380 // Allow the value to be set either with the 'value' element or
381 // with 'left_field'.
382 if (isset($info['left_field'])) {
383 $placeholder_sql = "$left[alias].$info[left_field]";
386 $arguments[$placeholder] = $info['value'];
389 // Set 'left field' as join table field is not set.
391 $join_table_field = "$left[alias].$info[left_field]";
392 $arguments[$placeholder] = $info['value'];
394 // Render out the SQL fragment with parameters.
395 return "$join_table_field $operator $placeholder_sql";