英文:
How to get 2 sums in single query in sql laravel
问题
我想在Laravel中的单个查询中实现2个结果（聚合函数）。
$user = Auth::guard('api')->user();
$data['current_month_score'] = UserScore::whereMonth('created_at', now()->month)
->where('user_id', $user->id)
->sum('score');
$data['current_month_score'] = UserScore::where('user_id', $user->id)
->sum('score');
这个代码给我正确的结果，但我想在一个查询中实现这个目标。如果可以的话，该如何实现？
英文:
I want to achieve 2 results (aggregate functions) in single query in laravel.
$user=Auth::guard('api')->user();
$data['current_month_score']= UserScore::whereMonth('created_at',now()->month)->where('user_id',$user->id)->sum('score');
$data['current_month_score']= UserScore::where('user_id',$user->id)->sum('score');
this gives me correct result but i want to achieve this in 1 query.
Is this possible if so, how ?
答案1
得分: 3
$user = Auth::guard('api')->user();
$data = UserScore::selectRaw('SUM(CASE WHEN MONTH(created_at) = ? THEN score ELSE 0 END) AS current_month_score, SUM(score) AS total_score', [now()->month])
->where('user_id', $user->id)
->first();
$data 变量将包含两个属性的对象：current_month_score 和 total_score。您可以使用 $data->current_month_score 和 $data->total_score 来访问这些值。
英文:
$user = Auth::guard('api')->user();
$data = UserScore::selectRaw('SUM(CASE WHEN MONTH(created_at) = ? THEN score ELSE 0 END) AS current_month_score, SUM(score) AS total_score', [now()->month])
->where('user_id', $user->id)
->first();
The $data variable will contain an object with two properties: current_month_score and total_score. You can access these values using $data->current_month_score and $data->total_score.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论