public function Select::__toString in Drupal driver for SQL Server and SQL Azure 8.2
Same name and namespace in other branches
- 8 drivers/lib/Drupal/Driver/Database/sqlsrv/Select.php \Drupal\Driver\Database\sqlsrv\Select::__toString()
- 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 229 - Definition of Drupal\Driver\Database\sqlsrv\Select
Class
Namespace
Drupal\Driver\Database\sqlsrvCode
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 ';
}
$has_range = !empty($this->range);
$order = $this->order;
// FIELDS and EXPRESSIONS
$fields = [];
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()
->getTableIntrospection($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)}].[{$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
->escapeField($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 = [];
$this->cross_apply_aliases = [];
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'] . ']';
}
}
}
// If this is a range query, we MUST specify an order...
if ($has_range && empty($order)) {
$fields[] = '1 as __tempsort';
if (!is_array($order)) {
$order = [];
}
$order['__tempsort'] = '';
}
$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.
$add_order_by = $this->order && (empty($this->inSubQuery) || !empty($this->range));
if ($add_order_by) {
$query .= "\nORDER BY ";
$fields = [];
foreach ($order as $field => $direction) {
$fields[] = $this->connection
->escapeField($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;
}