php - Laravel - 如何将相对表与限制结果相加?
问题描述
我有这两个模型。
我想得到表中 12 列comment_count
的总和youtube_video_insights
。
如何使用 Query Builder 或 Eloquent 做到这一点。
我尝试在我的存储库中使用类似的东西,但它没有用。
它只是对所有comment_count
列求和。
$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'];
}
解决方案
我假设您的表格结构是:
对于youtube_channel_insights的表,您有:
id | name
----------------------------------------
1 | channel_01
2 | channel_02
对于youtube_video_insights的表格,您有:
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
现在你应该像这样编写你的查询:
$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();