前提：
laravel自6.0后就明确了支持的SQL Server版本最低为2017，而SQL Server是在2012版本后，引入的offset语法来实现分页，在此之前只能使用ROW_NUMBER()函数来完成分页。问题：生产环境的
SQL Server由于历史原因，仍旧使用的2008版本，自然是不支持offset语法的，而新建项目使用的laravel版本为10，就不可避免遇到了分页报错问题
最终解决方案
-
自定义一个参数解析器
<?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 '';
}
}
-
让SQL Server链接使用这个参数解析器
<?php
namespace App\Models\SqlServer;
use Illuminate\Database\SqlServerConnection;
class SqlServerConnectionPolyfill extends SqlServerConnection
{
protected function getDefaultQueryGrammar()
{
return $this->withTablePrefix(new SqlServerGrammarPolyfill());
}
}
-
在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)
-
pagination with sqlsrv driver · laravel/framework · Discussion #43549
-
[8.x] Add proper paging offset when possible to sql server (#39863) · laravel/framework@beea2aa