如何在雄辩的orm中结合sum（）和group by语句？

Question

I want to produce record who retrieve data sales in specified date range with 2 parameter input $date_from and $date_to so I am trying to combine some SQL function to do that:

public function print_report(Request $request) {
    $from = "2018-04-03";
    $to = "2018-04-17";
    $data = AppSalesDetail::select('app_sales_detail.*','app_sales.*')
        ->leftJoin('app_sales','app_sales.app_sales_id','=','app_sales_detail.app_sales_id')    
        ->whereBetween('sale_date', array($from, $to))
        ->groupBy("app_sales_detail.app_sales_id")  
        ->sum('app_sales_detail.sub_total')
        ->get();
}

Before I add ->sum('app_sales_detail.sub_total') inside the query everything works fine as my expect but happen this error after I insert that function

Call to a member function groupBy() on string

please give me advice to solve that.

Answer 1

The sum() method only returns one row, you have to add it to select().
You also can't select all the columns:

$data = AppSalesDetail::select('app_sales_detail.app_sales_id')
    ->selectRaw('SUM(sub_total) sub_total')
    ->leftJoin('app_sales','app_sales.app_sales_id','=','app_sales_detail.app_sales_id')    
    ->whereBetween('sale_date', array($from, $to))
    ->groupBy("app_sales_detail.app_sales_id")  
    ->get();