Laravel - How To Sum Relative Table With Limit Result?

I have these 2 models.

I want to get sum of 12 columns comment_count in youtube_video_insights table.

How can I do that with Query Builder or Eloquent.

I try to use something like in my Repository this but it didn't work.

It's just sum all the comment_count columns.

$this->youtube_channel_insight
            ->join('youtube_video_insights',function ($q){
                $q->on('youtube_channel_insights.id','=','youtube_video_insights.youtube_channel_insight_id')
                    ->orderBy('youtube_video_insights.id','desc')
                    ->limit(12);
            })
            ->where('youtube_channel_insights.id',$id)
            ->select(\DB::raw(
                "SUM(youtube_video_insights.comment_count) as total_comment"
            ))->get();

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class YoutubeChannelInsight extends Model
{
    protected $guarded = ['id'];

    public function videos()
    {
        return $this->hasMany(YoutubeVideoInsight::class);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class YoutubeVideoInsight extends Model
{
    protected $guarded = ['id'];
}

I assume the structure of your tables are:

for youtube_channel_insights's table you have :

id | name
----------------------------------------
1  | channel_01
2  | channel_02

and for youtube_video_insights's table you have:

id | channel_id|    name    | comment_count
----------------------------------------
1  |     1     |  video_01  |      20
2  |     1     |  video_02  |      40
3  |     2     |  video_03  |      5
4  |     2     |  video_04  |      15

Now you should wright your query like this:

$sub1 = YoutubeVideoInsight ::select('comment_count', 'channel_id')
->whereRaw('channel_id = 2')->orderBy('id', 'Desc')->limit(2);

$sub2 = DB::table(DB::raw("({$sub1->toSql()}) as sub"))
->select(DB::raw('
SUM(`comment_count`) AS total_comment,
channel_id
'));


return $query = YoutubeChannelInsight ::joinSub($sub2,'sub2',function($join){
$join->on('channels.id','=','sub2.channel_id');
})->get();