Laravel Eloquent实现按剧集分组获取未观看的下一集

要实现每个追踪剧集下按季数、集数排序的第一个未观看剧集查询，核心是利用窗口函数+子查询的组合，绕开窗口函数不能直接用于WHERE的限制，同时在SQL层面完成过滤，避免全量加载的性能问题。

具体实现代码 #

$userId = $request->user()->id;
$trackedShowIds = $request->user()->trackedShows()->pluck('id')->toArray();

$nextEpisodes = Episode::query()
    // 基于子查询构建查询，子查询负责生成带行号的未观看剧集列表
    ->fromSub(function ($query) use ($userId, $trackedShowIds) {
        $query->select([
            'episodes.*',
            // 按show_id分组，季数、集数转成数字升序排序，生成行号
            \DB::raw('ROW_NUMBER() OVER (PARTITION BY episodes.show_id ORDER BY CAST(episodes.season AS UNSIGNED) ASC, CAST(episodes.number AS UNSIGNED) ASC) AS row_num')
        ])
        ->from('episodes')
        // 只包含用户追踪的剧集
        ->whereIn('episodes.show_id', $trackedShowIds)
        // 左关联用户观看记录，筛选未观看的剧集
        ->leftJoin('episode_user', function ($join) use ($userId) {
            $join->on('episode_user.episode_id', '=', 'episodes.id')
                ->where('episode_user.user_id', $userId);
        })
        ->whereNull('episode_user.episode_id');
    }, 'ranked_episodes')
    // 只取每个剧集分组里的第一条（行号为1）
    ->where('row_num', 1)
    ->get();

代码说明 #

1. 子查询逻辑：

   • 先筛选出用户追踪的所有未观看剧集（通过左关联episode_user并判断episode_id为NULL）。
   • 用ROW_NUMBER()窗口函数，按show_id分区，将每个剧集下的未观看内容按季数、集数的数字顺序排序，生成唯一行号。这里用CAST(xxx AS UNSIGNED)代替xxx * 1，是更规范的字符串转数字方式，避免隐式转换的潜在问题。

2. 外层查询逻辑：

   • 直接筛选行号为1的记录，也就是每个剧集下按季集排序后的第一个未观看剧集，完全符合需求。

3. 性能优势：

   • 所有过滤逻辑都在SQL层面完成，不会加载全量未观看剧集，即使数据量庞大也能保证性能。

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