addWhereExpression example

/** * Breaks the query with adding an invalid where expression. * * @ViewsFilter("test_exception_filter") */
class FilterExceptionTest extends FilterPluginBase {

  /** * {@inheritdoc} */
  public function query() {
    $this->query->addWhereExpression(NULL, "syntax error");
  }

}

class UidRevision extends Name {

  public function query($group_by = FALSE) {
    $this->ensureMyTable();

    $placeholder = $this->placeholder() . '[]';

    $args = array_values($this->value);

    $this->query->addWhereExpression($this->options['group'], "$this->tableAlias.uid IN($placeholder) OR ((SELECT COUNT(DISTINCT vid) FROM {node_revision} nr WHERE nr.revision_uid IN ($placeholder) AND nr.nid = $this->tableAlias.nid) > 0)", [$placeholder => $args],
      $args);
  }

}

$where = "$this->tableAlias.$this->realField ";

    if (empty($this->value)) {
      $where .= "= ''";
      if ($this->accept_null) {
        $where = '(' . $where . " OR $this->tableAlias.$this->realField IS NULL)";
      }
    }
    else {
      $where .= "<> ''";
    }
    $this->query->addWhereExpression($this->options['group'], $where);
  }

}

// Convert to ISO format and format for query. UTC timezone is used since     // dates are stored in UTC.     $a = new DateTimePlus($min, new \DateTimeZone($timezone));
    $a = $this->query->getDateFormat($this->query->getDateField("'" . $this->dateFormatter->format($a->getTimestamp() + $origin_offset, 'custom', DateTimeItemInterface::DATETIME_STORAGE_FORMAT, DateTimeItemInterface::STORAGE_TIMEZONE) . "'", TRUE, $this->calculateOffset), $this->dateFormat, TRUE);
    $b = new DateTimePlus($this->value['max'], new \DateTimeZone($timezone));
    $b = $this->query->getDateFormat($this->query->getDateField("'" . $this->dateFormatter->format($b->getTimestamp() + $origin_offset, 'custom', DateTimeItemInterface::DATETIME_STORAGE_FORMAT, DateTimeItemInterface::STORAGE_TIMEZONE) . "'", TRUE, $this->calculateOffset), $this->dateFormat, TRUE);

    // This is safe because we are manually scrubbing the values.     $operator = strtoupper($this->operator);
    $field = $this->query->getDateFormat($this->query->getDateField($field, TRUE, $this->calculateOffset), $this->dateFormat, TRUE);
    $this->query->addWhereExpression($this->options['group'], "$field $operator $a AND $b");
  }

  /** * Override parent method, which deals with dates as integers. */
  protected function opSimple($field) {
    $timezone = $this->getTimezone();
    $origin_offset = $this->getOffset($this->value['value'], $timezone);

    // Convert to ISO. UTC timezone is used since dates are stored in UTC.     $value = new DateTimePlus($this->value['value'], new \DateTimeZone($timezone));
    

class UidRevision extends Uid {

  public function query($group_by = FALSE) {
    $this->ensureMyTable();
    $placeholder = $this->placeholder();
    $this->query->addWhereExpression(0, "$this->tableAlias.uid = $placeholder OR ((SELECT COUNT(DISTINCT vid) FROM {node_revision} nr WHERE nr.revision_uid = $placeholder AND nr.nid = $this->tableAlias.nid) > 0)", [$placeholder => $this->argument]);
  }

}

          $join = Views::pluginManager('join')->createInstance('standard', $configuration);
          $entity_base_table_alias = $this->query->addRelationship($entity_base_table, $join, $entity_revision_base_table);
        }

        $bundle_condition = $this->view->query->getConnection()->condition('AND');
        $bundle_condition->condition("$entity_base_table_alias.{$entity_type->getKey('bundle')}", $moderated_bundles, 'IN');
      }
      // Otherwise, force the query to return an empty result.       else {
        $this->query->addWhereExpression($this->options['group'], '1 = 0');
        return;
      }
    }

    if ($this->operator === 'in') {
      $operator = "=";
    }
    else {
      $operator = "<>";
    }

    

    else {
      $this->value = [$this->argument];
    }

    $placeholder = $this->placeholder();
    $null_check = empty($this->options['not']) ? '' : " OR $this->tableAlias.$this->realField IS NULL";

    if (count($this->value) > 1) {
      $operator = empty($this->options['not']) ? 'IN' : 'NOT IN';
      $placeholder .= '[]';
      $this->query->addWhereExpression(0, "$this->tableAlias.$this->realField $operator($placeholder)" . $null_check, [$placeholder => $this->value]);
    }
    else {
      $operator = empty($this->options['not']) ? '=' : '!=';
      $this->query->addWhereExpression(0, "$this->tableAlias.$this->realField $operator $placeholder" . $null_check, [$placeholder => $this->argument]);
    }
  }

  /** * {@inheritdoc} */
  public function getSortName() {
    

$clause = '';
    $clause2 = '';
    if ($alias = $this->query->ensureTable('comment_entity_statistics', $this->relationship)) {
      $clause = "OR $alias.last_comment_timestamp > (***CURRENT_TIME*** - $limit)";
      $clause2 = "OR $field < $alias.last_comment_timestamp";
    }

    // NULL means a history record doesn't exist. That's clearly new content.     // Unless it's very very old content. Everything in the query is already     // type safe cause none of it is coming from outside here.     $this->query->addWhereExpression($this->options['group'], "($field IS NULL AND ($node.changed > (***CURRENT_TIME*** - $limit) $clause)) OR $field < $node.changed $clause2");
  }

  /** * {@inheritdoc} */
  public function adminSummary() {
    if (!empty($this->options['exposed'])) {
      return $this->t('exposed');
    }
  }

}

if ($formula) {
      $placeholder = $this->placeholder();
      if ($operator == 'IN') {
        $field .= " IN($placeholder)";
      }
      else {
        $field .= ' = ' . $placeholder;
      }
      $placeholders = [
        $placeholder => $argument,
      ];
      $this->query->addWhereExpression(0, $field, $placeholders);
    }
    else {
      $this->query->addWhere(0, $field, $argument, $operator);
    }
  }

  public function summaryArgument($data) {
    $value = $this->caseTransform($data->{$this->base_alias}, $this->options['path_case']);
    if (!empty($this->options['transform_dash'])) {
      $value = strtr($value, ' ', '-');
    }
    

return $errors;
  }

  /** * {@inheritdoc} */
  public function opEqual($expression) {
    // By default, things like opEqual uses add_where, that doesn't support     // complex expressions, so override opEqual (and all operators below).     $placeholder = $this->placeholder();
    $operator = $this->getConditionOperator($this->operator());
    $this->query->addWhereExpression($this->options['group'], "$expression $operator $placeholder", [$placeholder => $this->value]);
  }

  protected function opContains($expression) {
    $placeholder = $this->placeholder();
    $operator = $this->getConditionOperator('LIKE');
    $this->query->addWhereExpression($this->options['group'], "$expression $operator $placeholder", [$placeholder => '%' . $this->connection->escapeLike($this->value) . '%']);
  }

  /** * Filters by one or more words. * * By default opContainsWord uses add_where, that doesn't support complex * expressions. * * @param string $expression * The expression to add to the query. */

public function canExpose() {
    return FALSE;
  }

  public function query() {
    $table = $this->ensureMyTable();
    $snippet = "$table.status = 1 OR ($table.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0 AND ***VIEW_OWN_UNPUBLISHED_NODES*** = 1) OR ***BYPASS_NODE_ACCESS*** = 1";
    if ($this->moduleHandler->moduleExists('content_moderation')) {
      $snippet .= ' OR ***VIEW_ANY_UNPUBLISHED_NODES*** = 1';
    }
    $this->query->addWhereExpression($this->options['group'], $snippet);
  }

  /** * {@inheritdoc} */
  public function getCacheContexts() {
    $contexts = parent::getCacheContexts();

    $contexts[] = 'user';

    return $contexts;
  }

if ($formula) {
        $placeholder = $this->placeholder();
        if ($operator == 'IN') {
          $operator = "$operator IN($placeholder)";
        }
        else {
          $operator = "$operator $placeholder";
        }
        $placeholders = [
          $placeholder => $value,
        ];
        $this->handler->query->addWhereExpression($options['group'], "$field $operator", $placeholders);
      }
      else {
        $placeholder = $this->placeholder();
        if (count($this->handler->value) > 1) {
          $placeholder .= '[]';

          if ($operator == 'IS NULL') {
            $this->handler->query->addWhereExpression($options['group'], "$field $operator");
          }
          else {
            $this->handler->query->addWhereExpression($options['group'], "$field $operator($placeholder)", [$placeholder => $value]);
          }

  protected function opNotLike($field) {
    $operator = $this->getConditionOperator('NOT LIKE');
    $this->query->addWhere($this->options['group'], $field, '%' . $this->connection->escapeLike($this->value) . '%', $operator);
  }

  protected function opShorterThan($field) {
    $placeholder = $this->placeholder();
    // Type cast the argument to an integer because the SQLite database driver     // has to do some specific alterations to the query base on that data type.     $this->query->addWhereExpression($this->options['group'], "LENGTH($field) < $placeholder", [$placeholder => (int) $this->value]);
  }

  protected function opLongerThan($field) {
    $placeholder = $this->placeholder();
    // Type cast the argument to an integer because the SQLite database driver     // has to do some specific alterations to the query base on that data type.     $this->query->addWhereExpression($this->options['group'], "LENGTH($field) > $placeholder", [$placeholder => (int) $this->value]);
  }

  /** * Filters by a regular expression. * * @param string $field * The expression pointing to the queries field, for example "foo.bar". */

$b = intval(strtotime($this->value['max'], 0));

    if ($this->value['type'] == 'offset') {
      // Keep sign.       $a = '***CURRENT_TIME***' . sprintf('%+d', $a);
      // Keep sign.       $b = '***CURRENT_TIME***' . sprintf('%+d', $b);
    }
    // This is safe because we are manually scrubbing the values.     // It is necessary to do it this way because $a and $b are formulas when using an offset.     $operator = strtoupper($this->operator);
    $this->query->addWhereExpression($this->options['group'], "$field $operator $a AND $b");
  }

  protected function opSimple($field) {
    $value = intval(strtotime($this->value['value'], 0));
    if (!empty($this->value['type']) && $this->value['type'] == 'offset') {
      // Keep sign.       $value = '***CURRENT_TIME***' . sprintf('%+d', $value);
    }
    // This is safe because we are manually scrubbing the value.     // It is necessary to do it this way because $value is a formula when using an offset.     $this->query->addWhereExpression($this->options['group'], "$field $this->operator $value");
  }

  /** * {@inheritdoc} */
  public function query() {
    $table = $this->ensureMyTable();
    $snippet = "$table.status = 1 OR ($table.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0 AND ***VIEW_OWN_UNPUBLISHED_MEDIA*** = 1) OR ***ADMINISTER_MEDIA*** = 1";
    if ($this->moduleHandler->moduleExists('content_moderation')) {
      $snippet .= ' OR ***VIEW_ANY_UNPUBLISHED_NODES*** = 1';
    }
    $this->query->addWhereExpression($this->options['group'], $snippet);
  }

  /** * {@inheritdoc} */
  public function getCacheContexts() {
    $contexts = parent::getCacheContexts();
    $contexts[] = 'user';
    return $contexts;
  }

}