解决高版本laravel/framework中SQLServer2008分页报错问题

前提：laravel自6.0后就明确了支持的SQL Server版本最低为2017，而SQL Server是在2012版本后，引入的offset语法来实现分页，在此之前只能使用ROW_NUMBER()函数来完成分页。

问题：生产环境的SQL Server由于历史原因，仍旧使用的2008版本，自然是不支持offset语法的，而新建项目使用的laravel版本为10，就不可避免遇到了分页报错问题

最终解决方案

PS: 请忽略我的命名空间，你想放到哪都行的，我这纯属懒
另外：这只是一个临时的方案，不确定有没有其他问题，最好的办法还是升级数据库，或者整个降级回去使用旧版本laravel，但两个办法动静都有点大，自己权衡吧

1. 自定义一个参数解析器

<?php
namespace App\Models\SqlServer;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\SqlServerGrammar;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\Log;
// 引用了 laravel/framework 旧版本的参数解析相关代码
class SqlServerGrammarPolyfill extends SqlServerGrammar
{
 public function __construct()
 {
 // 在这里写了条日志，方便验证是否被调用了，用完删掉
 Log::info('Using custom sqlserver2008 parameter parser');
 }
 public function compileSelect(Builder $query): string
 {
 if (! $query->offset) {
 return parent::compileSelect($query);
 }
 if (is_null($query->columns)) {
 $query->columns = ['*'];
 }
 $components = $this->compileComponents($query);
 // 这里注释掉了下面这个判断，否则当有排序时，仍然会使用OFFSET去做分页
 // if (! empty($components['orders'])) {
 // return parent::compileSelect($query)." offset {$query->offset} rows fetch next {$query->limit} rows only";
 // }
 // If an offset is present on the query, we will need to wrap the query in
 // a big "ANSI" offset syntax block. This is very nasty compared to the
 // other database systems but is necessary for implementing features.
 return $this->compileAnsiOffset(
 $query, $components
 );
 }
 protected function compileAnsiOffset(Builder $query, $components): string
 {
 // An ORDER BY clause is required to make this offset query work, so if one does
 // not exist we'll just create a dummy clause to trick the database and so it
 // does not complain about the queries for not having an "order by" clause.
 if (empty($components['orders'])) {
 $components['orders'] = 'order by (select 0)';
 }
 // We need to add the row number to the query so we can compare it to the offset
 // and limit values given for the statements. So we will add an expression to
 // the "select" that will give back the row numbers on each of the records.
 $components['columns'] .= $this->compileOver($components['orders']);
 unset($components['orders']);
 if ($this->queryOrderContainsSubquery($query)) {
 $query->bindings = $this->sortBindingsForSubqueryOrderBy($query);
 }
 // Next we need to calculate the constraints that should be placed on the query
 // to get the right offset and limit from our query but if there is no limit
 // set we will just handle the offset only since that is all that matters.
 $sql = $this->concatenate($components);
 return $this->compileTableExpression($sql, $query);
 }
 protected function compileOver($orderings): string
 {
 return ", row_number() over ({$orderings}) as row_num";
 }
 protected function queryOrderContainsSubquery($query): bool
 {
 if (! is_array($query->orders)) {
 return false;
 }
 return Arr::first($query->orders, function ($value) {
 return $this->isExpression($value['column'] ?? null);
 }, false) !== false;
 }
 protected function sortBindingsForSubqueryOrderBy($query): array
 {
 return Arr::sort($query->bindings, function ($bindings, $key) {
 return array_search($key, ['select', 'order', 'from', 'join', 'where', 'groupBy', 'having', 'union', 'unionOrder']);
 });
 }
 protected function compileTableExpression($sql, $query): string
 {
 $constraint = $this->compileRowConstraint($query);
 return "select * from ({$sql}) as temp_table where row_num {$constraint} order by row_num";
 }
 protected function compileRowConstraint($query): string
 {
 $start = (int) $query->offset + 1;
 if ($query->limit > 0) {
 $finish = (int) $query->offset + (int) $query->limit;
 return "between {$start} and {$finish}";
 }
 return ">= {$start}";
 }
 /**
 * Compile the "limit" portions of the query.
 *
 * @param \Illuminate\Database\Query\Builder $query
 * @param int $limit
 * @return string
 */
 protected function compileLimit(Builder $query, $limit)
 {
 return '';
 }
 /**
 * Compile the "offset" portions of the query.
 *
 * @param \Illuminate\Database\Query\Builder $query
 * @param int $offset
 * @return string
 */
 protected function compileOffset(Builder $query, $offset)
 {
 return '';
 }
}	

2. 让SQL Server链接使用这个参数解析器

<?php
namespace App\Models\SqlServer;
use Illuminate\Database\SqlServerConnection;
class SqlServerConnectionPolyfill extends SqlServerConnection
{
 protected function getDefaultQueryGrammar()
 {
 return $this->withTablePrefix(new SqlServerGrammarPolyfill());
 }
}

3. 在AppServiceProvider中注册你自定义的解析器

<?php
namespace App\Providers;
use App\Models\SqlServer\SqlServerConnectionPolyfill;
use Illuminate\Database\Connection;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
 /**
 * Register any application services.
 */
 public function register(): void
 {
 //
 Connection::resolverFor('sqlsrv', function ($connection, $database, $prefix, $config) {
 return new SqlServerConnectionPolyfill($connection, $database, $prefix, $config);
 });
 }
 /**
 * Bootstrap any application services.
 */
 public function boot(): void
 {
 ...
 }
}

参考链接: (感谢伟大的gayhub,感谢laracasts)

1. [Pagination On DIfferent SQL Server Versions]

2. pagination with sqlsrv driver · laravel/framework · Discussion #43549

3. framework/src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php at beea2aaffb8b2bc4c2a348abeee306904c6fd32c · laravel/framework

4. [8.x] Add proper paging offset when possible to sql server (#39863) · laravel/framework@beea2aa

5. [8.x] Add proper paging offset when possible to sql server by joelharkes · Pull Request #39863 · laravel/framework