php - PHP Laravel 按月和年从数据库中提取数据

Question

你好，我有这个函数，它总结了budget_cost 并按月获取数据

public function marktingCost(){

$costs = \DB::table('campaigns')
    ->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month'))
    ->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year'))
    ->groupBy('campaign_leadsource_id')
    ->where('campaign_status_id',4) // campaign_status_id = 4 means campaign completed
    ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month)
    ->get(); return $costs}

我试图实现的是按月获取数据作为budget_total_month，并按年获取数据作为budget_total_year，但是如果查询中的条件我想做这样的事情，我不能使用

->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month') ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month))
->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year') ->where(\DB::raw('Year(created_at)'), Carbon::today()->year))

但这当然是无效的

我想要的输出是

[{"campaign_leadsource_id":1,"budget_total_month":11475,"budget_total_year":134761,"olxTotal":12,"budget_per_lead":11230},{"campaign_leadsource_id":2,"budget_total_month":4221,"budget_total_year":41215,"olxTotal":9,"budget_per_lead":4579}]

先感谢您

标签： phpmysqllaravel

Answer 1

请尝试此代码。

$costs = \DB::table('campaigns')
        ->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month'))
        ->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year'))

        ->where('campaign_status_id',4) // campaign_status_id = 4 means campaign completed
        ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month)
        ->groupBy(\DB::raw("MONTH(created_at)"))
        ->get();