public function Connection::addRangeToQuery in Drupal driver for SQL Server and SQL Azure 8.2

Same name and namespace in other branches

1. 8 drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php \Drupal\Driver\Database\sqlsrv\Connection::addRangeToQuery()

Internal function: add range options to a query.

This cannot be set protected because it is used in other parts of the database engine.

@status tested

1 call to Connection::addRangeToQuery()

Connection::queryRange in drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php

Runs a limited-range query on this database object.

File

drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php, line 759

Definition of Drupal\Driver\Database\sqlsrv\Connection

Class

Connection

Temporary tables: temporary table support is done by means of global temporary tables (#) to avoid the use of DIRECT QUERIES. You can enable and disable the use of direct queries with $this->driver_settings->defaultDirectQuery =…

Namespace

Drupal\Driver\Database\sqlsrv

Code

public function addRangeToQuery($query, $from, $count) {
  if ($from == 0) {

    // Easy case: just use a TOP query if we don't have to skip any rows.
    $query = preg_replace('/^\\s*SELECT(\\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(' . $count . ')', $query);
  }
  else {
    if ($this->connection
      ->Scheme()
      ->EngineVersionNumber() >= 11) {
      if (strripos($query, 'ORDER BY') === false) {
        $query = "SELECT Q.*, 0 as TempSort FROM ({$query}) as Q ORDER BY TempSort OFFSET {$from} ROWS FETCH NEXT {$count} ROWS ONLY";
      }
      else {
        $query = "{$query} OFFSET {$from} ROWS FETCH NEXT {$count} ROWS ONLY";
      }
    }
    else {

      // More complex case: use a TOP query to retrieve $from + $count rows, and
      // filter out the first $from rows using a window function.
      $query = preg_replace('/^\\s*SELECT(\\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(' . ($from + $count) . ') ', $query);
      $query = '
          SELECT * FROM (
            SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.__line2) AS __line3 FROM (
              SELECT sub1.*, 1 AS __line2 FROM (' . $query . ') AS sub1
            ) as sub2
          ) AS sub3
          WHERE __line3 BETWEEN ' . ($from + 1) . ' AND ' . ($from + $count);
    }
  }
  return $query;
}