我有一个利润表在我的数据库,当我按日期从数据库中获取我需要的数据进行筛选利润(为today
,last 7 days
,last 30 days
)。
在我的控制器中,我尝试过
$todayProfit = Profit::whereDate('created_at',today())->get();
return response()->json($todayProfit);
但它只提供24小时,但我今天需要获利。
当我使用检索过去30天的数据时,
$lastMonthProfit = Profit::whereDate('created_at',today()->subDay(30))->get();
return response()->json($lastMonthProfit );
它对我不起作用,我该如何解决
一次计算全部的查询可能是;
SELECT 'Today' as date, sum(profit) as total FROM profits
WHERE created_at > DATE_ADD(CURDATE(), INTERVAL -1 DAY)
UNION
SELECT 'Last 7 days', sum(profit) FROM profits
WHERE created_at > DATE_ADD(CURDATE(), INTERVAL -7 DAY)
UNION
SELECT 'Last 30 days', sum(profit) FROM profits
WHERE created_at > DATE_ADD(CURDATE(), INTERVAL -30 DAY);
查询生成器版本;
return Profit::where('created_at', '>', DB::raw('DATE_ADD(CURDATE(), INTERVAL -1 DAY)'))
->select([DB::raw('"Today" as date'), DB::raw('sum(profit) as total')])
->union(
Profit::where('created_at', '>', DB::raw('DATE_ADD(CURDATE(), INTERVAL -7 DAY)'))
->select([DB::raw('"Last 7 days"'), DB::raw('sum(profit) as total')])
)
->union(
Profit::where('created_at', '>', DB::raw('DATE_ADD(CURDATE(), INTERVAL -30 DAY)'))
->select([DB::raw('"Last 30 days"'), DB::raw('sum(profit) as total')])
)
->get();
它打印出类似这些的东西;
[
{
"date": "Today",
"total": "50"
},
{
"date": "Last 7 days",
"total": "100"
},
{
"date": "Last 30 days",
"total": "310"
}
]
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句