Laravel子查询分页如何避免SQL Server出现ORDER BY子句无效错误

环境说明 #

• Laravel版本：8.55.0
• SQL Server版本：2017

解决方案 #

以下两种方案均可解决该问题，可根据业务场景选择：

方案1：快速绕过（无需修改底层代码）

SQL Server 要求子查询/视图中使用 ORDER BY 时必须搭配 TOP、OFFSET 或 FOR XML 语句，我们可以给分页子查询加一个不影响结果的超大 TOP 限制，满足语法要求即可：

// 构造pagination子查询时增加take的超大值参数
$paginationSub = PersonView::select('person_id')
    ->groupBy('person_id')
    ->orderBy('person_id')
    ->take(9223372036854775807) // SQL Server bigint类型最大值，不会过滤任何正常分页的结果
    ->offset($offset)
    ->limit($limit);

// 后续继续使用fromSub、leftJoinSub组合查询即可
$finalQuery = DB::query()
    ->select('main.*')
    ->fromSub($paginationSub, 'pagination')
    ->leftJoinSub(PersonView::query(), 'main', 'pagination.person_id', '=', 'main.person_id')
    ->with($eagerLoads)
    ->select($selects);

方案2：全局修复（重写SQL Server分页语法）

如果项目中多处出现同类问题，可以自定义SQL Server查询语法类，移除多余的子查询ORDER BY row_num逻辑：

1. 新建自定义语法类 app/Database/Grammars/CustomSqlServerGrammar.php

<?php

namespace App\Database\Grammars;

use Illuminate\Database\Query\Grammars\SqlServerGrammar as BaseSqlServerGrammar;
use Illuminate\Database\Query\Builder;

class CustomSqlServerGrammar extends BaseSqlServerGrammar
{
    /**
     * 重写offset编译逻辑，移除子查询中多余的ORDER BY row_num
     */
    protected function compileOffset(Builder $query, $offset)
    {
        if ($query->offset > 0) {
            return "WHERE row_num BETWEEN " . ((int) $offset + 1) . " AND " . ((int) $offset + (int) $query->limit);
        }

        return '';
    }
}

2. 在 app/Providers/AppServiceProvider.php 的 boot 方法中注册自定义语法：

public function boot()
{
    DB::connection('sqlsrv')->setQueryGrammar(new \App\Database\Grammars\CustomSqlServerGrammar);
}

注意事项 #

• 方案1对业务代码侵入小，适合临时修复单个查询的问题；方案2适合全局统一处理所有同类分页报错，无需修改业务查询代码。
• 两种方案均不会影响原有分页的结果准确性，也不会导致查询性能下降。

内容的提问来源于stack exchange，提问作者shmuels