如何计算laravel中特定id的总额？

Question

我有两张表发票和invoice_items。我想要的是和金额为每个发票id和显示在我的看法。

发票表

• id

invoice_items表

>

id

发票号

数量

我想为具体的发票id求和金额列。我对laravel是新来的，所以我如何使用laravel eloquent做到这一点。

Answer 1

Try with this one. I think this works
$result = \DB::table('Invoice')     
    ->join('invoice_items', 'Invoice.id', 
'=','invoice_items.invoice_id')->whereIn('Invoice.id', [1, 2, 3]))->sum('invoice_items.amount');    

if somewhere i am wrong try with exchanging table name.

Answer 2

假设在您的发票模型中有这样的关系。

public function invoice_items() [
   return $this->hasMany('App\Invoice_Items', 'invoice_id');
}

在控制器中，代码应该如下所示。

获取所有发票及其发票项总金额

$invoices = Invoice::with(['invoice_items' => function($query){
   $query->sum('amount');
}])->get();

return view('invoice', compact('invoices'));

获取包含发票项目总金额的特定发票

$invoice = Invoice::with(['invoice_items' => function($query){
       $query->sum('amount');
}])->find($invoice_id);

return view('invoice', compact('invoices'));

Answer 3

因为你使用的是一对多。

您可以将与和groupBy一起使用，如下所示：

$invoice = Invoice::with(['invoice_items' => function($query){
       $query->groupBy('invoice_id')->select('invoice_id', DB::raw('SUM(amount) AS amount_sum'));
}])->get();

或者您可以使用leftjoin和SUM如下所示：

$invoice_query = InvoiceItem::groupBy('invoice_id')->select("invoice_id", DB::raw('SUM(amount) AS amount_sum'));

Invoice::leftjoin(DB::raw("({$invoice_query->toSql()}) AS ii"), 'ii.invoice_id', '=', 'invoices.id')
       ->mergeBindings($invoice_query->getQuery())
       ->select('invoices.*', 'amount_sum')
       ->get();

在发票模型中使用存取器：

protected $appends = ['amount_sum'];
public function getAmountSumAttribute()
{
    return $this->invoice_items()->where('invoice_id', $this->id)->sum('amount');
}

因此，您可以得到金额的总和：

Invoice::all()->toJSON();