public function Select::__toString in Drupal driver for SQL Server and SQL Azure 8

Same name and namespace in other branches

1. 8.2 drivers/lib/Drupal/Driver/Database/sqlsrv/Select.php \Drupal\Driver\Database\sqlsrv\Select::__toString()
2. 3.0.x drivers/lib/Drupal/Driver/Database/sqlsrv/Select.php \Drupal\Driver\Database\sqlsrv\Select::__toString()

Implements PHP magic __toString method to convert the query to a string.

The toString operation is how we compile a query object to a prepared statement.

Return value

string A prepared statement query string for this object.

Overrides Select::__toString

File

drivers/lib/Drupal/Driver/Database/sqlsrv/Select.php, line 249

Definition of Drupal\Driver\Database\sqlsrv\Select

Class

Select

Namespace

Drupal\Driver\Database\sqlsrv

Code

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 ';
  }

  // FIELDS and EXPRESSIONS
  $fields = array();
  foreach ($this->tables as $alias => $table) {

    // Table might be a subquery, so nothing to do really.
    if (is_string($table['table']) && !empty($table['all_fields'])) {

      // Temporary tables are not supported here.
      if ($table['table'][0] == '#') {
        $fields[] = $this->connection
          ->escapeTable($alias) . '.*';
      }
      else {
        $info = $this->connection
          ->schema()
          ->queryColumnInformation($table['table']);

        // Some fields need to be "transparent" to Drupal, including technical primary keys
        // or custom computed columns.
        foreach ($info['columns_clean'] as $column) {
          $fields[] = $this->connection
            ->escapeTable($alias) . '.' . $this->connection
            ->escapeField($column['name']);
        }
      }
    }
  }
  foreach ($this->fields as $alias => $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']);
  }

  // In MySQL you can reuse expressions present in SELECT
  // from WHERE.
  // The way to emulate that behaviour in SQL Server is to
  // fit all that in a CROSS_APPLY with an alias and then consume
  // it from WHERE or AGGREGATE.
  $cross_apply = array();
  $this->cross_apply_aliases = array();
  foreach ($this->expressions as $alias => $expression) {

    // Only use CROSS_APPLY for non-aggregate expresions. This trick
    // will not work, and does not make sense, for aggregates.
    // If the alias is 'expression' this is Drupal's default
    // meaning that more than probably this expression
    // is never reused in a WHERE.
    if ($expression['expand'] !== FALSE && $expression['alias'] != 'expression' && $this
      ->stripos_arr($expression['expression'], array(
      'AVG(',
      'GROUP_CONCAT(',
      'COUNT(',
      'MAX(',
      'GROUPING(',
      'GROUPING_ID(',
      'COUNT_BIG(',
      'CHECKSUM_AGG(',
      'MIN(',
      'SUM(',
      'VAR(',
      'VARP(',
      'STDEV(',
      'STDEVP(',
    )) === FALSE) {

      // What we are doing here is using a CROSS APPLY to
      // generate an expression that can be used in the select and where
      // but we need to give this expression a new name.
      $cross_apply[] = "\nCROSS APPLY (SELECT " . $expression['expression'] . ' cross_sqlsrv) cross_' . $expression['alias'];
      $new_alias = 'cross_' . $expression['alias'] . '.cross_sqlsrv';

      // We might not want an expression to appear in the select list.
      if ($expression['exclude'] !== TRUE) {
        $fields[] = $new_alias . ' AS ' . $expression['alias'];
      }

      // Store old expression and new representation.
      $this->cross_apply_aliases[$expression['alias']] = 'cross_' . $expression['alias'] . '.cross_sqlsrv';
    }
    else {

      // We might not want an expression to appear in the select list.
      if ($expression['exclude'] !== TRUE) {
        $fields[] = $expression['expression'] . ' AS [' . $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 DatabaseSelectInterface) {

      // 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']) . '}';
    }

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

  // CROSS APPLY
  $query .= implode($cross_apply);

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

    // There is an implicit string cast on $this->condition.
    $where = (string) $this->condition;

    // References to expressions in cross-apply need to be updated.
    // Now we need to update all references to the expression aliases
    // and point them to the CROSS APPLY alias.
    if (!empty($this->cross_apply_aliases)) {
      $regex = str_replace('{0}', implode('|', array_keys($this->cross_apply_aliases)), self::RESERVED_REGEXP_BASE);

      // Add and then remove the SELECT
      // keyword. Do this to use the exact same
      // regex that we have in DatabaseConnection_sqlrv.
      $where = 'SELECT ' . $where;
      $where = preg_replace_callback($regex, array(
        $this,
        'replaceReservedAliases',
      ), $where);
      $where = substr($where, 7, strlen($where) - 7);
    }
    $query .= "\nWHERE ( " . $where . " )";
  }

  // GROUP BY
  if ($this->group) {
    $group = $this->group;

    // You named it, if the newly expanded expression
    // is added to the select list, then it must
    // also be present in the aggregate expression.
    $group = array_merge($group, $this->cross_apply_aliases);
    $query .= "\nGROUP BY " . implode(', ', $group);
  }

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

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

  // 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.
  if ($this->order && (empty($this->inSubQuery) || !empty($this->range))) {
    $query .= "\nORDER BY ";
    $fields = array();
    foreach ($this->order as $field => $direction) {
      $fields[] = $field . ' ' . $direction;
    }
    $query .= implode(', ', $fields);
  }

  // RANGE
  if (!empty($this->range)) {
    $query = $this->connection
      ->addRangeToQuery($query, $this->range['start'], $this->range['length']);
  }

  // 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'];
    }
  }
  return $query;
}