You are here

Home » API reference » Drupal driver for SQL Server and SQL Azure 4.0.x » Select.php

class Select in Drupal driver for SQL Server and SQL Azure 4.0.x

Same name and namespace in other branches
  1. 4.2.x src/Driver/Database/sqlsrv/Select.php \Drupal\sqlsrv\Driver\Database\sqlsrv\Select
  2. 3.1.x src/Driver/Database/sqlsrv/Select.php \Drupal\sqlsrv\Driver\Database\sqlsrv\Select
  3. 4.1.x src/Driver/Database/sqlsrv/Select.php \Drupal\sqlsrv\Driver\Database\sqlsrv\Select

Hierarchy

Expanded class hierarchy of Select

2 files declare their use of Select
ConditionTest.php in tests/src/Kernel/ConditionTest.php
OrderByTest.php in tests/src/Unit/OrderByTest.php

File

src/Driver/Database/sqlsrv/Select.php, line 15

Namespace

Drupal\sqlsrv\Driver\Database\sqlsrv
View source 
class Select extends QuerySelect {

  /**
   * The connection object on which to run this query.
   *
   * @var \Drupal\sqlsrv\Driver\Database\sqlsrv\Connection
   */
  protected $connection;

  /**
   * Is this statement in a subquery?
   *
   * @var bool
   */
  protected $inSubQuery = FALSE;

  /**
   * Adds an expression to the list of "fields" to be SELECTed.
   *
   * An expression can be any arbitrary string that is valid SQL. That includes
   * various functions, which may in some cases be database-dependent. This
   * method makes no effort to correct for database-specific functions.
   *
   * Overriden with an aditional exclude parameter that tells not to include
   * this expression (by default) in the select list.
   *
   * Drupal expects the AVG() function to return a decimal number. SQL Server
   * will return the FLOOR instead. We multiply the expression by 1.0 to force
   * a cast inside the AVG function. `AVG(m.id)` becomes `AVG(m.id * 1.0)`.
   *
   * @param string $expression
   *   The expression string. May contain placeholders.
   * @param string $alias
   *   The alias for this expression. If not specified, one will be generated
   *   automatically in the form "expression_#". The alias will be checked for
   *   uniqueness, so the requested alias may not be the alias that is assigned
   *   in all cases.
   * @param mixed $arguments
   *   Any placeholder arguments needed for this expression.
   * @param bool $exclude
   *   If set to TRUE, this expression will not be added to the select list.
   *   Useful when you want to reuse expressions in the WHERE part.
   * @param bool $expand
   *   If this expression will be expanded as a CROSS_JOIN so it can be consumed
   *   from other parts of the query. TRUE by default. It attempts to detect
   *   expressions that cannot be cross joined (aggregates).
   *
   * @return string
   *   The unique alias that was assigned for this expression.
   */
  public function addExpression($expression, $alias = NULL, $arguments = [], $exclude = FALSE, $expand = TRUE) {
    $sub_expression = $expression;
    $replacement_expression = '';
    while (strlen($sub_expression) > 5 && ($pos1 = stripos($sub_expression, 'AVG(')) !== FALSE) {
      $pos2 = $this
        ->findParenMatch($sub_expression, $pos1 + 3);
      $inner = substr($sub_expression, $pos1 + 4, $pos2 - 4 - $pos1);
      $replacement_expression .= substr($sub_expression, 0, $pos1 + 4) . '(' . $inner . ') * 1.0)';
      if (strlen($sub_expression) > $pos2 + 1) {
        $sub_expression = substr($sub_expression, $pos2 + 1);
      }
      else {
        $sub_expression = '';
      }
    }
    $replacement_expression .= $sub_expression;
    $alias = parent::addExpression($replacement_expression, $alias, $arguments);
    $this->expressions[$alias]['exclude'] = $exclude;
    $this->expressions[$alias]['expand'] = $expand;
    return $alias;
  }

  /**
   * Given a string find the matching parenthesis after the given point.
   *
   * @param string $string
   *   The input string.
   * @param int $start_paren
   *   The 0 indexed position of the open-paren, for which we would like
   *   to find the matching closing-paren.
   *
   * @return int|false
   *   The 0 indexed position of the close paren.
   */
  private function findParenMatch($string, $start_paren) {
    if ($string[$start_paren] !== '(') {
      return FALSE;
    }
    $str_array = str_split(substr($string, $start_paren + 1));
    $paren_num = 1;
    foreach ($str_array as $i => $char) {
      if ($char == '(') {
        $paren_num++;
      }
      elseif ($char == ')') {
        $paren_num--;
      }
      if ($paren_num == 0) {
        return $i + $start_paren + 1;
      }
    }
    return FALSE;
  }

  /**
   * {@inheritdoc}
   */
  public function preExecute(SelectInterface $query = NULL) {

    // If no query object is passed in, use $this.
    if (!isset($query)) {
      $query = $this;
    }

    // Only execute this once.
    if ($this
      ->isPrepared()) {
      return TRUE;
    }

    // Execute standard pre-execution first.
    parent::preExecute($query);
    if ($this->distinct || $this->group) {

      // When the query is DISTINCT or contains GROUP BY fields, all the fields
      // in the GROUP BY and ORDER BY clauses must appear in the returned
      // columns.
      $columns = $this->order + array_flip($this->group);
      $counter = 0;
      foreach ($columns as $field => $dummy) {
        $found = FALSE;
        foreach ($this->fields as $f) {
          if (!isset($f['table']) || !isset($f['field'])) {
            continue;
          }
          $alias = "{$f['table']}.{$f['field']}";
          if ($alias == $field) {
            $found = TRUE;
            break;
          }
        }
        if (!isset($this->fields[$field]) && !isset($this->expressions[$field]) && !$found) {
          $alias = '_field_' . $counter++;
          $this
            ->addExpression($field, $alias, [], FALSE, FALSE);
          $this->queryOptions['sqlsrv_drop_columns'][] = $alias;
        }
      }

      // The other way round is also true, if using aggregates, all the fields
      // in the SELECT must be present in the GROUP BY.
      if (!empty($this->group)) {
        foreach ($this->fields as $field) {
          $spec = $field['table'] . '.' . $field['field'];
          $alias = $field['alias'];
          if (!isset($this->group[$spec]) && !isset($this->group[$alias])) {
            $this->group[$spec] = $spec;
          }
        }
      }

      // More over, GROUP BY columns cannot use aliases, so expand them to
      // their full expressions.
      foreach ($this->group as $key => &$group_field) {

        // Expand an alias on a field.
        if (isset($this->fields[$group_field])) {
          $field = $this->fields[$group_field];
          $group_field = (isset($field['table']) ? $this->connection
            ->escapeTable($field['table']) . '.' : '') . $this->connection
            ->escapeField($field['field']);
        }
        elseif (isset($this->expressions[$group_field])) {
          $expression = $this->expressions[$group_field];
          $group_field = $expression['expression'];
        }
      }
    }
    $this->queryOptions['emulate_prepares'] = TRUE;
    return $this->prepared;
  }

  /**
   * {@inheritdoc}
   *
   * Why this is needed?
   */
  public function compile(DatabaseConnection $connection, PlaceholderInterface $queryPlaceholder) {
    $this->inSubQuery = $queryPlaceholder != $this;
    return parent::compile($connection, $queryPlaceholder);
  }

  /**
   * {@inheritdoc}
   *
   * Overridden to support SQL Server Range Query syntax and CROSS APPLY.
   */
  public function __toString() {

    // For convenience, we compile the query ourselves if the caller forgot
    // to do it. This allows constructs like "(string) $query" to work. When
    // the query will be executed, it will be recompiled using the proper
    // placeholder generator anyway.
    if (!$this
      ->compiled()) {
      $this
        ->compile($this->connection, $this);
    }

    // Create a sanitized comment string to prepend to the query.
    $comments = $this->connection
      ->makeComment($this->comments);

    // SELECT.
    $query = $comments . 'SELECT ';
    if ($this->distinct) {
      $query .= 'DISTINCT ';
    }
    $used_range = FALSE;
    if (!empty($this->range) && $this->range['start'] == 0 && !$this->union && isset($this->range['length'])) {
      $query .= 'TOP (' . $this->range['length'] . ') ';
      $used_range = TRUE;
    }

    // FIELDS and EXPRESSIONS.
    $fields = [];
    foreach ($this->tables as $alias => $table) {
      if (!empty($table['all_fields'])) {
        $fields[] = $this->connection
          ->escapeAlias($alias) . '.*';
      }
    }
    foreach ($this->fields as $field) {

      // Always use the AS keyword for field aliases, as some
      // databases require it (e.g., PostgreSQL).
      $fields[] = (isset($field['table']) ? $this->connection
        ->escapeTable($field['table']) . '.' : '') . $this->connection
        ->escapeField($field['field']) . ' AS ' . $this->connection
        ->escapeAlias($field['alias']);
    }
    foreach ($this->expressions as $expression) {
      $fields[] = $expression['expression'] . ' AS ' . $this->connection
        ->escapeAlias($expression['alias']);
    }
    $query .= implode(', ', $fields);

    // FROM - We presume all queries have a FROM, as any query that doesn't
    // won't need the query builder anyway.
    $query .= "\nFROM";
    foreach ($this->tables as $alias => $table) {
      $query .= "\n";
      if (isset($table['join type'])) {
        $query .= $table['join type'] . ' JOIN ';
      }

      // If the table is a subquery, compile it and integrate it into this
      // query.
      if ($table['table'] instanceof SelectInterface) {

        // Run preparation steps on this sub-query before converting to string.
        $subquery = $table['table'];
        $subquery
          ->preExecute();
        $table_string = '(' . (string) $subquery . ')';
      }
      else {
        $table_string = $this->connection
          ->escapeTable($table['table']);

        // Do not attempt prefixing cross database / schema queries.
        if (strpos($table_string, '.') === FALSE) {
          $table_string = '{' . $table_string . '}';
        }
      }

      // Don't use the AS keyword for table aliases, as some
      // databases don't support it (e.g., Oracle).
      $query .= $table_string . ' ' . $this->connection
        ->escapeAlias($table['alias']);
      if (!empty($table['condition'])) {
        $query .= ' ON ' . $table['condition'];
      }
    }

    // WHERE.
    if (count($this->condition)) {

      // There is an implicit string cast on $this->condition.
      $query .= "\nWHERE " . $this->condition;
    }

    // GROUP BY.
    if ($this->group) {
      $query .= "\nGROUP BY " . implode(', ', $this->group);
    }

    // HAVING.
    if (count($this->having)) {

      // There is an implicit string cast on $this->having.
      $query .= "\nHAVING " . $this->having;
    }

    // UNION is a little odd, as the select queries to combine are passed into
    // this query, but syntactically they all end up on the same level.
    if ($this->union) {
      foreach ($this->union as $union) {
        $query .= ' ' . $union['type'] . ' ' . (string) $union['query'];
      }
    }

    // ORDER BY.
    // The ORDER BY clause is invalid in views, inline functions, derived
    // tables, subqueries, and common table expressions, unless TOP or FOR XML
    // is also specified.
    $add_order_by = $this->order && (empty($this->inSubQuery) || !empty($this->range));
    if ($add_order_by) {
      $query .= "\nORDER BY ";
      $fields = [];
      foreach ($this->order as $field => $direction) {
        $fields[] = $this->connection
          ->escapeField($field) . ' ' . $direction;
      }
      $query .= implode(', ', $fields);
    }

    // RANGE.
    if (!empty($this->range) && !$used_range) {
      if (!$add_order_by) {
        $query .= " ORDER BY (SELECT NULL)";
      }
      $query .= " OFFSET {$this->range['start']} ROWS FETCH NEXT {$this->range['length']} ROWS ONLY";
    }
    return $query;
  }

  /**
   * Override of SelectQuery::orderRandom() for SQL Server.
   *
   * It seems that sorting by RAND() doesn't actually work, this is a less then
   * elegant workaround.
   *
   * @status tested
   */
  public function orderRandom() {
    $alias = $this
      ->addExpression('NEWID()', 'random_field');
    $this
      ->orderBy($alias);
    return $this;
  }

  /**
   * Mark Alises.
   *
   * Does not return anything, so should not be called 'getUsedAliases'
   */
  private function getUsedAliases(DatabaseCondition $condition, array &$aliases = []) {
    foreach ($condition
      ->conditions() as $key => $c) {
      if (is_string($key) && substr($key, 0, 1) == '#') {
        continue;
      }
      if (is_a($c['field'], DatabaseCondition::class)) {
        $this
          ->GetUsedAliases($c['field'], $aliases);
      }
      else {
        $aliases[$c['field']] = TRUE;
      }
    }
  }

  /**
   * Prepare a count query.
   *
   * This is like the default prepareCountQuery, but does not optimize field (or
   * expressions) that are being used in conditions. (Why not?)
   *
   * @return mixed
   *   A Select object.
   */
  protected function prepareCountQuery() {

    // Create our new query object that we will mutate into a count query.
    $count = clone $this;
    $group_by = $count
      ->getGroupBy();
    $having = $count
      ->havingConditions();
    if (!$count->distinct && !isset($having[0])) {
      $used_aliases = [];
      $this
        ->getUsedAliases($count->condition, $used_aliases);

      // When not executing a distinct query, we can zero-out existing fields
      // and expressions that are not used by a GROUP BY or HAVING. Fields
      // listed in a GROUP BY or HAVING clause need to be present in the
      // query.
      $fields =& $count
        ->getFields();
      foreach ($fields as $field => $value) {
        if (empty($group_by[$field]) && !isset($used_aliases[$value['alias']])) {
          unset($fields[$field]);
        }
      }
      $expressions =& $count
        ->getExpressions();
      foreach ($expressions as $field => $value) {
        if (empty($group_by[$field]) && !isset($used_aliases[$value['alias']])) {
          unset($expressions[$field]);
        }
      }

      // Also remove 'all_fields' statements, which are expanded into
      // tablename.* when the query is executed.
      foreach ($count->tables as $alias => &$table) {
        unset($table['all_fields']);
      }
    }

    // If we've just removed all fields from the query, make sure there is at
    // least one so that the query still runs.
    $count
      ->addExpression('1');

    // Ordering a count query is a waste of cycles, and breaks on some
    // databases anyway.
    $orders =& $count
      ->getOrderBy();
    $orders = [];
    if ($count->distinct && !empty($group_by)) {

      // If the query is distinct and contains a GROUP BY, we need to remove the
      // distinct because SQL99 does not support counting on distinct multiple
      // fields.
      $count->distinct = FALSE;
    }
    return $count;
  }

}

Members

Namesort descending Modifiers Type Description Overrides
Query::$comments protected property An array of comments that can be prepended to a query.
Query::$connectionKey protected property The key of the connection object.
Query::$connectionTarget protected property The target of the connection object.
Query::$nextPlaceholder protected property The placeholder counter.
Query::$queryOptions protected property The query options to pass on to the connection object.
Query::$uniqueIdentifier protected property A unique identifier for this query object.
Query::comment public function Adds a comment to the query.
Query::getComments public function Returns a reference to the comments array for the query.
Query::getConnection public function Gets the database connection to be used for the query.
Query::nextPlaceholder public function Gets the next placeholder value for this query object. Overrides PlaceholderInterface::nextPlaceholder
Query::uniqueIdentifier public function Returns a unique identifier for this object. Overrides PlaceholderInterface::uniqueIdentifier
Query::__sleep public function Implements the magic __sleep function to disconnect from the database.
Query::__wakeup public function Implements the magic __wakeup function to reconnect to the database.
QueryConditionTrait::$condition protected property The condition object for this query.
QueryConditionTrait::alwaysFalse public function
QueryConditionTrait::andConditionGroup public function
QueryConditionTrait::condition public function
QueryConditionTrait::conditionGroupFactory public function
QueryConditionTrait::conditions public function
QueryConditionTrait::exists public function
QueryConditionTrait::isNotNull public function
QueryConditionTrait::isNull public function
QueryConditionTrait::notExists public function
QueryConditionTrait::orConditionGroup public function
QueryConditionTrait::where public function
Select::$connection protected property The connection object on which to run this query. Overrides Query::$connection
Select::$distinct protected property Whether or not this query should be DISTINCT.
Select::$expressions protected property The expressions to SELECT as virtual fields.
Select::$fields protected property The fields to SELECT.
Select::$forUpdate protected property The FOR UPDATE status. 1
Select::$group protected property The fields by which to group.
Select::$having protected property The conditional object for the HAVING clause.
Select::$inSubQuery protected property Is this statement in a subquery?
Select::$order protected property The fields by which to order this query.
Select::$prepared protected property Indicates if preExecute() has already been called.
Select::$range protected property The range limiters for this query.
Select::$tables protected property The tables against which to JOIN.
Select::$union protected property An array whose elements specify a query to UNION, and the UNION type. The 'type' key may be '', 'ALL', or 'DISTINCT' to represent a 'UNION', 'UNION ALL', or 'UNION DISTINCT'…
Select::addExpression public function Adds an expression to the list of "fields" to be SELECTed. Overrides Select::addExpression
Select::addField public function Adds a field to the list to be SELECTed. Overrides SelectInterface::addField
Select::addJoin public function Join against another table in the database. Overrides SelectInterface::addJoin
Select::addMetaData public function Adds additional metadata to the query. Overrides AlterableInterface::addMetaData
Select::addTag public function Adds a tag to a query. Overrides AlterableInterface::addTag
Select::arguments public function Gets a complete list of all values to insert into the prepared statement. Overrides QueryConditionTrait::arguments
Select::compile public function Why this is needed? Overrides Select::compile
Select::compiled public function Check whether a condition has been previously compiled. Overrides QueryConditionTrait::compiled
Select::countQuery public function Get the equivalent COUNT query of this query as a new query object. Overrides SelectInterface::countQuery
Select::distinct public function Sets this query to be DISTINCT. Overrides SelectInterface::distinct
Select::escapeField public function Escapes a field name string. Overrides SelectInterface::escapeField
Select::escapeLike public function Escapes characters that work as wildcard characters in a LIKE pattern. Overrides SelectInterface::escapeLike
Select::execute public function Runs the query against the database. Overrides Query::execute 1
Select::extend public function Enhance this object by wrapping it in an extender object. Overrides ExtendableInterface::extend
Select::fields public function Add multiple fields from the same table to be SELECTed. Overrides SelectInterface::fields
Select::findParenMatch private function Given a string find the matching parenthesis after the given point.
Select::forUpdate public function Add FOR UPDATE to the query. Overrides SelectInterface::forUpdate 1
Select::getArguments public function Compiles and returns an associative array of the arguments for this prepared statement. Overrides SelectInterface::getArguments
Select::getExpressions public function Returns a reference to the expressions array for this query. Overrides SelectInterface::getExpressions
Select::getFields public function Returns a reference to the fields array for this query. Overrides SelectInterface::getFields
Select::getGroupBy public function Returns a reference to the group-by array for this query. Overrides SelectInterface::getGroupBy
Select::getMetaData public function Retrieves a given piece of metadata. Overrides AlterableInterface::getMetaData
Select::getOrderBy public function Returns a reference to the order by array for this query. Overrides SelectInterface::getOrderBy
Select::getTables public function Returns a reference to the tables array for this query. Overrides SelectInterface::getTables
Select::getUnion public function Returns a reference to the union queries for this query. This include queries for UNION, UNION ALL, and UNION DISTINCT. Overrides SelectInterface::getUnion
Select::getUsedAliases private function Mark Alises.
Select::groupBy public function Groups the result set by the specified field. Overrides SelectInterface::groupBy
Select::hasAllTags public function Determines if a given query has all specified tags. Overrides AlterableInterface::hasAllTags
Select::hasAnyTag public function Determines if a given query has any specified tag. Overrides AlterableInterface::hasAnyTag
Select::hasTag public function Determines if a given query has a given tag. Overrides AlterableInterface::hasTag
Select::having public function Adds an arbitrary HAVING clause to the query. Overrides SelectInterface::having
Select::havingArguments public function Gets a list of all values to insert into the HAVING clause. Overrides SelectInterface::havingArguments
Select::havingCompile public function Compiles the HAVING clause for later retrieval. Overrides SelectInterface::havingCompile
Select::havingCondition public function Helper function to build most common HAVING conditional clauses. Overrides SelectInterface::havingCondition
Select::havingConditions public function Gets a list of all conditions in the HAVING clause. Overrides SelectInterface::havingConditions
Select::havingExists public function Sets a HAVING condition that the specified subquery returns values. Overrides SelectInterface::havingExists
Select::havingIsNotNull public function Sets a condition in the HAVING clause that the specified field be NOT NULL. Overrides SelectInterface::havingIsNotNull
Select::havingIsNull public function Sets a condition in the HAVING clause that the specified field be NULL. Overrides SelectInterface::havingIsNull
Select::havingNotExists public function Sets a HAVING condition that the specified subquery returns no values. Overrides SelectInterface::havingNotExists
Select::innerJoin public function Inner Join against another table in the database. Overrides SelectInterface::innerJoin
Select::isPrepared public function Indicates if preExecute() has already been called on that object. Overrides SelectInterface::isPrepared
Select::join public function Default Join against another table in the database. Overrides SelectInterface::join
Select::leftJoin public function Left Outer Join against another table in the database. Overrides SelectInterface::leftJoin
Select::orderBy public function Orders the result set by a given field. Overrides SelectInterface::orderBy 1
Select::orderRandom public function Override of SelectQuery::orderRandom() for SQL Server. Overrides Select::orderRandom
Select::preExecute public function Generic preparation and validation for a SELECT query. Overrides Select::preExecute
Select::prepareCountQuery protected function Prepare a count query. Overrides Select::prepareCountQuery
Select::range public function Restricts a query to a given range in the result set. Overrides SelectInterface::range
Select::union public function Add another Select query to UNION to this one. Overrides SelectInterface::union
Select::__clone public function Implements the magic __clone function. Overrides Query::__clone
Select::__construct public function Constructs a Select object. Overrides Query::__construct
Select::__toString public function Overridden to support SQL Server Range Query syntax and CROSS APPLY. Overrides Select::__toString