能否简化查询适配paginate？无需自定义分页器且兼容Query Builder

我有一组用户消息数据，需要获取每个用户发布的最早消息，之后对结果使用paginate()方法。示例数据如下：

id | user_id |     message    | date_posted
1  |    5    |  some_message  |  2022-07-15  
2  |   125   |  some_message  |  2022-08-02  
3  |    5    |  some_message  |  2022-04-05 

需求是仅获取ID为2和3的数据行。当前用DB::select(DB::raw($query))执行的查询返回数组，无法使用paginate()方法，现有SQL语句为：

select T.*
from (select *,
             row_number() over (partition by user_id order by date_posted, id) as sn
      from my_table
     ) T
where sn = 1;

请问能否将其转换为Query Builder或Eloquent语句实现？注：无法关闭only_full_group_by配置。

解决方案 #

1. Query Builder 实现方式 #

利用Laravel的fromSub()方法构建子查询，完全匹配原生SQL逻辑，同时支持分页：

// 构建子查询：给每个用户的消息按发布时间+ID排序并编号
$subquery = DB::table('my_table')
    ->selectRaw('*, row_number() over (partition by user_id order by date_posted, id) as sn');

// 主查询：筛选编号为1的记录（即每个用户的最早消息）并分页
$paginatedResults = DB::table($subquery, 'T')
    ->where('sn', 1)
    ->paginate(15); // 替换为你需要的每页条数

2. Eloquent 模型实现方式 #

如果有对应的消息模型（比如App\Models\Message），写法类似：

use App\Models\Message;

$subquery = Message::selectRaw('*, row_number() over (partition by user_id order by date_posted, id) as sn');

$paginatedResults = Message::fromSub($subquery, 'T')
    ->where('sn', 1)
    ->paginate(15);

关键说明 #

• fromSub()方法支持将子查询作为主查询的数据源，既保留了窗口函数的筛选逻辑，又能返回支持paginate()的查询构建器实例。
• 这种写法完全兼容only_full_group_by配置，因为没有使用不符合分组规则的聚合操作，而是通过窗口函数精准筛选每个用户的第一条消息。

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