如何选择集数最高的相关字幕视频

Question

我有一个子主题表和一个视频表，它们是相关的。我的视频表看起来像下面的

|id| sub_topic_id| episode | title   | description |
|1 |      1      |      1  | Hello   |    Test     |
|2 |      1      |     10  | Hello2  |    Test     |
|3 |      2      |      1  | Hello3  |    Test     |

我现在想要的是获得every subtopic和highest episode number的视频，并在它们上分页。在我的例子中，子主题1得到10，子主题2得到1。

我希望我的意思很清楚，也许有人能帮我。我现在尝试的是

$videos = DB::table('videos')->where('episode', DB::raw("(select max(`episode`) from videos)"))->paginate(10);

Answer 1

这是查询；

SELECT videos.*
FROM videos
JOIN (
SELECT sub_topic_id, MAX(episode) AS maxEpisode
FROM videos
GROUP BY sub_topic_id) AS subQuery
ON subQuery.maxEpisode = videos.episode AND subQuery.sub_topic_id = videos.sub_topic_id;

这是一个雄辩的版本；

$subQuery = DB::table('videos')
->groupBy('sub_topic_id')
->select('sub_topic_id', DB::raw('MAX(episode) as maxEpisode'));
return Video::join(DB::raw('(' . $subQuery->toSql() . ') as subQuery'), function ($join) {
$join->on('subQuery.maxEpisode', '=', 'videos.episode');
$join->on('subQuery.sub_topic_id', '=', 'videos.sub_topic_id');
})->get(['videos.*']);

Answer 2

将剧集列结构设置为integre，然后尝试

$videos = DB::table('videos')->orderby('episode',DESC)->paginate(10);