ci-bonfire /
Sprint
| @@ 297-344 (lines=48) @@ | ||
| 294 | * @param string $sql SQL Query |
|
| 295 | * @return string |
|
| 296 | */ |
|
| 297 | protected function _limit($sql) |
|
| 298 | { |
|
| 299 | // As of SQL Server 2012 (11.0.*) OFFSET is supported |
|
| 300 | if (version_compare($this->version(), '11', '>=')) |
|
| 301 | { |
|
| 302 | // SQL Server OFFSET-FETCH can be used only with the ORDER BY clause |
|
| 303 | empty($this->qb_orderby) && $sql .= ' ORDER BY 1'; |
|
| 304 | ||
| 305 | return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; |
|
| 306 | } |
|
| 307 | ||
| 308 | $limit = $this->qb_offset + $this->qb_limit; |
|
| 309 | ||
| 310 | // An ORDER BY clause is required for ROW_NUMBER() to work |
|
| 311 | if ($this->qb_offset && ! empty($this->qb_orderby)) |
|
| 312 | { |
|
| 313 | $orderby = $this->_compile_order_by(); |
|
| 314 | ||
| 315 | // We have to strip the ORDER BY clause |
|
| 316 | $sql = trim(substr($sql, 0, strrpos($sql, $orderby))); |
|
| 317 | ||
| 318 | // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results |
|
| 319 | if (count($this->qb_select) === 0) |
|
| 320 | { |
|
| 321 | $select = '*'; // Inevitable |
|
| 322 | } |
|
| 323 | else |
|
| 324 | { |
|
| 325 | // Use only field names and their aliases, everything else is out of our scope. |
|
| 326 | $select = array(); |
|
| 327 | $field_regexp = ($this->_quoted_identifier) |
|
| 328 | ? '("[^\"]+")' : '(\[[^\]]+\])'; |
|
| 329 | for ($i = 0, $c = count($this->qb_select); $i < $c; $i++) |
|
| 330 | { |
|
| 331 | $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m) |
|
| 332 | ? $m[1] : $this->qb_select[$i]; |
|
| 333 | } |
|
| 334 | $select = implode(', ', $select); |
|
| 335 | } |
|
| 336 | ||
| 337 | return 'SELECT '.$select." FROM (\n\n" |
|
| 338 | .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) |
|
| 339 | ."\n\n) ".$this->escape_identifiers('CI_subquery') |
|
| 340 | ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; |
|
| 341 | } |
|
| 342 | ||
| 343 | return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); |
|
| 344 | } |
|
| 345 | ||
| 346 | // -------------------------------------------------------------------- |
|
| 347 | ||
| @@ 458-505 (lines=48) @@ | ||
| 455 | * @param string $sql SQL Query |
|
| 456 | * @return string |
|
| 457 | */ |
|
| 458 | protected function _limit($sql) |
|
| 459 | { |
|
| 460 | // As of SQL Server 2012 (11.0.*) OFFSET is supported |
|
| 461 | if (version_compare($this->version(), '11', '>=')) |
|
| 462 | { |
|
| 463 | // SQL Server OFFSET-FETCH can be used only with the ORDER BY clause |
|
| 464 | empty($this->qb_orderby) && $sql .= ' ORDER BY 1'; |
|
| 465 | ||
| 466 | return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; |
|
| 467 | } |
|
| 468 | ||
| 469 | $limit = $this->qb_offset + $this->qb_limit; |
|
| 470 | ||
| 471 | // An ORDER BY clause is required for ROW_NUMBER() to work |
|
| 472 | if ($this->qb_offset && ! empty($this->qb_orderby)) |
|
| 473 | { |
|
| 474 | $orderby = $this->_compile_order_by(); |
|
| 475 | ||
| 476 | // We have to strip the ORDER BY clause |
|
| 477 | $sql = trim(substr($sql, 0, strrpos($sql, $orderby))); |
|
| 478 | ||
| 479 | // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results |
|
| 480 | if (count($this->qb_select) === 0) |
|
| 481 | { |
|
| 482 | $select = '*'; // Inevitable |
|
| 483 | } |
|
| 484 | else |
|
| 485 | { |
|
| 486 | // Use only field names and their aliases, everything else is out of our scope. |
|
| 487 | $select = array(); |
|
| 488 | $field_regexp = ($this->_quoted_identifier) |
|
| 489 | ? '("[^\"]+")' : '(\[[^\]]+\])'; |
|
| 490 | for ($i = 0, $c = count($this->qb_select); $i < $c; $i++) |
|
| 491 | { |
|
| 492 | $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m) |
|
| 493 | ? $m[1] : $this->qb_select[$i]; |
|
| 494 | } |
|
| 495 | $select = implode(', ', $select); |
|
| 496 | } |
|
| 497 | ||
| 498 | return 'SELECT '.$select." FROM (\n\n" |
|
| 499 | .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) |
|
| 500 | ."\n\n) ".$this->escape_identifiers('CI_subquery') |
|
| 501 | ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; |
|
| 502 | } |
|
| 503 | ||
| 504 | return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); |
|
| 505 | } |
|
| 506 | ||
| 507 | // -------------------------------------------------------------------- |
|
| 508 | ||
