How do I select related subtopic videos with the highest episode number?

Question

I have a subtopics table and a videos table and they are related. My videos table looks like the following

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

What I want now is to get the video for every subtopic with the highest episode number and paginate on them. In my example I would get 10 for the subtopic 1 and 1 for the subtopic 2.

I hope it is clear what I mean, maybe someone could help me. What I have tried in eloquent for now is

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

Answer 1

This is the query;

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;

This is the eloquent version;

$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.*']);