Laravel中orderByRaw配合分页时排序结果随分页大小变化的问题

现象 #

使用Laravel查询构造器时，通过orderByRaw自定义排序规则，再结合paginate分页，会出现查询结果排序随分页大小改变的问题。

代码示例：

$query->orderByRaw("CASE WHEN tracking_eta IS NOT NULL THEN tracking_eta ELSE arrival_date END DESC")
        ->paginate(23)
        ->withQueryString();

当分页大小设为23时，返回结果为一种排序顺序；设为24时，排序发生混乱。经核实，顺序错乱的两条记录（id:219与id:218）的tracking_eta和arrival_date值完全一致：

id: 219 arrival_date: 2024-09-17 06:00:00
        tracking_eta: 2024-09-17 08:58:00

id: 218 arrival_date: 2024-09-17 06:00:00
        tracking_eta: 2024-09-17 08:58:00

这会引发翻页异常：id218在第一页是最后一条，切换到第二页变成第一条，id219则看似“消失”。

原因 #

当排序字段的值完全相同时，数据库没有明确的规则来确定这些记录的相对位置，会根据底层存储的物理位置或查询执行时的临时逻辑返回结果，导致不同分页场景下，同值记录的顺序可能随机变化，进而出现分页时记录重复或丢失的问题。

解决方案 #

在现有排序条件后追加一个唯一且稳定的排序字段（通常用主键id），确保即使前面的排序字段值相同，数据库也能按照固定顺序排列记录。

方案1：追加独立的orderBy #

$query->orderByRaw("CASE WHEN tracking_eta IS NOT NULL THEN tracking_eta ELSE arrival_date END DESC")
        ->orderBy('id', 'DESC') // 用主键固定同值记录的顺序
        ->paginate(23)
        ->withQueryString();

方案2：整合到orderByRaw中 #

$query->orderByRaw("CASE WHEN tracking_eta IS NOT NULL THEN tracking_eta ELSE arrival_date END DESC, id DESC")
        ->paginate(23)
        ->withQueryString();

通过以上方式，所有记录的排序顺序会完全固定，不会随分页大小变化而混乱，分页时的记录展示也会保持一致。

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