php - 如何根据另外两个表的计算从一个表中检索数据？

Question

假设我有三个模型，分别命名为Customer、Invoice 和Payment。发票和付款模型看起来像

id，customer_id，金额

我只想得到那些客户 Invoice.sum(amount)>Payment.sum(amount) 具有这些金额差异

我目前正在检索

$customers=Customer::get();
foreach($customers as $customer)
{  
  $pay=Payment::where('customer_id',$customer->id)->sum('amount');
  $due=Invoice::where('customer_id',$customer->id)->sum('amount');
  if($due>$pay){
   // showing this customers 
   }
}

Eloquent Join 有没有更好的方法？我怎样才能在 laravel 中变得 Eloquent ？

Answer 1

您是否在模型中设置了任何关系？一个更好的 Eloquent 查询将如下所示。您可能需要稍微调整一下

Customer::join('payment','customer.id','=','payment.customer_id')
          ->join('invoice','customer.id','=','invoice.customer_id')
          ->select(array('customer.*'),DB::raw("SUM(payment.amount) as payment_sum,SUM(invoice.amount) as invoice_sum"))
          //->where('customer_id',$customer->id)
          ->groupBy('customer.id')  //replace with anything that make sense for you.
          ->havingRaw('invoice_sum > payment_sum')
          ->get();