Laravel Eloquent实现按日期排序并通过rank_index固定产品位置

要实现直接从数据库层面完成指定排序，无需PHP处理集合，我们可以利用SQL的CTE（公共表表达式）和窗口函数来实现。核心思路是：生成所有连续的位置序号，将有rank_index的产品匹配到对应位置，剩余位置按created_at倒序填充无rank_index的产品，最后按位置排序。

实现代码（Laravel Eloquent/Query Builder） #

适用于MySQL 8.0+ / PostgreSQL等支持CTE和窗口函数的数据库：

use Illuminate\Support\Facades\DB;
use App\Models\Product;

$products = Product::query()
    // 生成从1到产品总数的连续位置序列
    ->withExpression('positions', function ($query) {
        $query->select(DB::raw('1 AS pos'))
              ->unionAll(function ($query) {
                  $query->select(DB::raw('pos + 1'))
                        ->from('positions')
                        ->whereRaw('pos < (SELECT COUNT(*) FROM products)');
              });
    })
    // 合并有rank和无rank的产品，分配最终排序位置
    ->withExpression('ranked_products', function ($query) {
        // 处理有rank_index的产品，直接用rank_index作为排序位置
        $rankedPart = $query->newQuery()
            ->select('p.*', 'p.rank_index AS sort_pos')
            ->from('products AS p')
            ->whereNotNull('p.rank_index');

        // 处理无rank_index的产品，分配到未被占用的位置
        $unrankedPart = $query->newQuery()
            ->select('p.*', 'avail.pos AS sort_pos')
            ->from(function ($sub) {
                // 给无rank的产品按created_at倒序编号
                $sub->select('*', DB::raw('ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn'))
                    ->from('products')
                    ->whereNull('rank_index');
            }, 'p')
            ->join(function ($sub) {
                // 找出未被rank_index占用的位置并编号
                $sub->select('pos', DB::raw('ROW_NUMBER() OVER (ORDER BY pos) AS rn'))
                    ->from('positions')
                    ->whereRaw('pos NOT IN (SELECT rank_index FROM products WHERE rank_index IS NOT NULL)');
            }, 'avail', 'p.rn', '=', 'avail.rn');

        $query->unionAll($rankedPart, $unrankedPart);
    })
    ->select('title', 'rank_index', 'created_at')
    ->from('ranked_products')
    ->orderBy('sort_pos')
    ->get();

关键说明 #

1. 位置序列生成：通过递归CTE生成从1到产品总数的连续位置，确保每个产品都有对应的位置。
2. 有rank产品处理：直接将rank_index作为最终排序位置。
3. 无rank产品处理：先按created_at倒序给无rank产品编号，再将这些编号匹配到未被占用的位置，实现按原倒序填充空位。
4. 约束建议：
   • 给rank_index字段添加唯一约束，避免重复位置导致排序异常。
   • 在业务逻辑中限制rank_index的取值范围为1到当前产品总数，防止位置溢出。

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