public function Select::__toString in Drupal driver for SQL Server and SQL Azure 3.0.x

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. 8 drivers/lib/Drupal/Driver/Database/sqlsrv/Select.php \Drupal\Driver\Database\sqlsrv\Select::__toString()

Overridden to support SQL Server Range Query syntax and CROSS APPLY.

Overrides Select::__toString

File

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

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