Make the conditions in joins in dynamic queries use Condition objects

Closes #3398773

core/lib/Drupal/Core/Database/Query/Condition.php

@@ -127,6 +127,71 @@ public function condition($field, $value = NULL, $operator = '=') {
     return $this;
   }
 
+  /**
+   * Compare two database fields with each other.
+   *
+   * This method is used in joins to compare 2 fields from different tables to
+   * each other.
+   *
+   * @param string $field
+   *   The name of the field to compare.
+   * @param string $field2
+   *   The name of the other field to compare.
+   * @param string|null $operator
+   *   (optional) The operator to use. The supported operators are: =, <>, <,
+   *   <=, >, >=, <>.
+   *
+   * @return $this
+   *   The called object.
+   *
+   * @throws \Drupal\Core\Database\InvalidQueryException
+   *   If passed invalid arguments, such as an empty array as $value.
+   */
+  public function compare(string $field, string $field2, ?string $operator = '='): self {
+    if (empty($operator)) {
+      $operator = '=';
+    }
+    if (!in_array($operator, ['=', '<', '>', '>=', '<=', '<>'], TRUE)) {
+      throw new InvalidQueryException(sprintf("In a query compare '%s %s %s' the operator must be one of the following: '=', '<', '>', '>=', '<=', '<>'.", $field, $operator, $field2));
+    }
+
+    $this->conditions[] = [
+      'field' => $field,
+      'field2' => $field2,
+      'operator' => $operator,
+    ];
+
+    $this->changed = TRUE;
+
+    return $this;
+  }
+
+  /**
+   * Resolve the alias placeholder in the condition and its children.
+   *
+   * @param string $placeholder
+   *   The value of the placeholder.
+   * @param string $alias
+   *   The value to replace the placeholder.
+   *
+   * @internal
+   */
+  public function resolveAlias(string $placeholder, string $alias): void {
+    foreach ($this->conditions as &$condition) {
+      if (isset($condition['field']) && $condition['field'] instanceof ConditionInterface) {
+        $condition['field']->resolveAlias($placeholder, $alias);
+      }
+      else {
+        if (isset($condition['field'])) {
+          $condition['field'] = str_replace($placeholder, $alias, $condition['field']);
+        }
+        if (isset($condition['field2'])) {
+          $condition['field2'] = str_replace($placeholder, $alias, $condition['field2']);
+        }
+      }
+    }
+  }
+
   /**
    * {@inheritdoc}
    */
@@ -225,7 +290,7 @@ public function compile(Connection $connection, PlaceholderInterface $queryPlace
           // @see ConditionInterface::condition() method (and thus have the
           // default value as defined over there) it is assumed to be a valid
           // condition on its own: ignore the operator and value parts.
-          $ignore_operator = $condition['operator'] === '=' && $condition['value'] === NULL;
+          $ignore_operator = ($condition['operator'] === '=' && $condition['value'] === NULL) || ($condition['operator'] === NULL && $condition['value'] === []);
         }
         elseif (!isset($condition['operator'])) {
           // Left hand part is a literal string added with the
@@ -281,7 +346,13 @@ public function compile(Connection $connection, PlaceholderInterface $queryPlace
 
         // Process value.
         $value_fragment = '';
-        if ($operator['use_value']) {
+        if (isset($condition['field2'])) {
+          // The key field2 is only set when we are comparing two fields with
+          // each other. The value part of the condition will be the second
+          // field.
+          $value_fragment = $connection->escapeField($condition['field2']);
+        }
+        elseif ($operator['use_value']) {
           // For simplicity, we first convert to an array, so that we can handle
           // the single and multi value cases the same.
           if (!is_array($condition['value'])) {

[mondrake]

Terminology - Personally, I prefer 'column' over 'field' when it comes to a database table.

[daffie]

I picked field, because in the rest of the existing class Drupal\Core\Database\Query\Condition it is all about fields and not about columns. If this was a greenfield solution, I would go with column, only it is not. Again: if you after reading this still think we should change something, then I will do that.

[mondrake]

I see here 'field' is preexisting, so agree we should not touch it but rather become 'field' => $columnLeft,

Is there a more meaningful name for 'field2'? 'compareField', or something

[daffie]

I picked field2, because in the rest of the existing class Drupal\Core\Database\Query\Condition it is all about fields and not about columns. If this was a greenfield solution, I would go with column, only it is not. Again: if you after reading this still think we should change something, then I will do that.