Laravel Sum 来自两个不同表的多次乘法的雄辩总和

Question

目前，在我的Laravel项目控制器中，我正在使用一个查询

查询 1

public function cartreview(Request $request,$sp_id, $service_id,$cart_id)
{
$total = DB::table('pricings')
->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.shirt*carts.quantity_shirt ) AS total'))                      
->where('pricings.sp_id', '=', $sp_id)
->where('carts.id', '=' , $cart_id)
->first();
}

在上面的查询中，我使用两个数据库表作为定价和购物车，其中我通过从定价表中获取价格和从购物车表中获取数量来计算衬衫项目的总账单价格。

现在我还想添加另一个带有衬衫的项目，例如裤子、领带等。 如何将更多的乘法传递给总和？

请帮我处理语法。我可以做这样的事情吗

查询 2

$total = DB::table('pricings')
->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.shirt*carts.quantity_shirt ,
pricings.pant*carts.quantity_pant , 
pricings.tie*carts.quantity_tie) AS total'))                      
->where('pricings.sp_id', '=', $sp_id)
->where('carts.id', '=' , $cart_id)
->first();

或者即使我为每个项目单独计算总计如何添加它？

$total_shirt = DB::table('pricings')
->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.shirt*carts.quantity_shirt ) AS total_shirt'))                      
->where('pricings.sp_id', '=', $sp_id)
->where('carts.id', '=' , $cart_id)
->first();
$total_pant = DB::table('pricings')
->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.pant*carts.quantity_pant ) AS total_pant'))                      
->where('pricings.sp_id', '=', $sp_id)
->where('carts.id', '=' , $cart_id)
->first();
$total_tie = DB::table('pricings')
->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.tie*carts.quantity_tie ) AS total_tie'))                      
->where('pricings.sp_id', '=', $sp_id)
->where('carts.id', '=' , $cart_id)
->first();

$total = $total_衬衫 + $total_裤子 + $total_领带; ？

要在view.blade中显示值.php我使用类似{{$total->total}}

提前谢谢。

试：

$waftotal = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(
pricings.Regular_Laundry*carts.q_Regular_Laundry,
pricings.Bedding_Mattress_Duvet_Cover*carts.q_Bedding_Mattress_Duvet_Cover,
pricings.Bedding_Comforter_laundry*carts.q_Bedding_Comforter_laundry,
pricings.Bedding_Blanket_Throw*carts.q_Bedding_Blanket_Throw,
pricings.Bedding_Pillow_laundry*carts.q_Bedding_Pillow_laundry,
pricings.Bath_Mat_laundry*carts.q_Bath_Mat_laundry,
pricings.Every_Hang_Dry_Item*carts.q_Every_Hang_Dry_Item
) AS waftotal'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();

但它给了我错误

SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误;请查看与您的 MariaDB 服务器版本相对应的手册，了解在 ' 定价附近使用的正确语法。第1 行的 Bedding_Mattress_Duvet_Cover c' (SQL：选择 sum(定价。Regular_Laundrycarts.q_Regular_Laundry，定价。Bedding_Mattress_Duvet_Covercarts.q_Bedding_Mattress_Duvet_Cover，定价。Bedding_Comforter_laundrycarts.q_Bedding_Comforter_laundry，定价。Bedding_Blanket_Throw carts.q_Bedding_Blanket_Throw，定价。Bedding_Pillow_laundrycarts.q_Bedding_Pillow_laundry，定价。Bath_Mat_laundry carts.q_Bath_Mat_laundry，定价。Every_Hang_Dry_Itemcarts.q_Every_Hang_Dry_Item ) AS waftotal 从pricings内部连接cartscarts.sp_id=pricings.sp_idpricings的地方.sp_id= 1 和carts.id= 23 限制 1)

即使我编写单独的查询

$waf1 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Regular_Laundry*carts.q_Regular_Laundry) AS waf1'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();

$waf2 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Bedding_Mattress_Duvet_Cover*carts.q_Bedding_Mattress_Duvet_Cover) AS waf2'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();

$waf3 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Bedding_Comforter_laundry*carts.q_Bedding_Comforter_laundry) AS waf3'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();

$waf4 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Bedding_Blanket_Throw*carts.q_Bedding_Blanket_Throw) AS waf4'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();
$waf5 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Bedding_Pillow_laundry*carts.q_Bedding_Pillow_laundry) AS waf5'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();
$waf6 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Bath_Mat_laundry*carts.q_Bath_Mat_laundry) AS waf6'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();
$waf7 = DB::table('pricings')->join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
->select(DB::raw('sum(pricings.Every_Hang_Dry_Item*carts.q_Every_Hang_Dry_Item) AS waf7'))
->where('pricings.sp_id', '=', $sp_id)->where('carts.id', '=' , $cart_id)->first();
$waftotal = $waf1->waf1 + $waf2->waf2 + $waf3->waf3 + $waf4->waf4 + $waf5->waf5 + $waf6->waf6 + $waf7->waf7 ;

在view.blade中.php{{$waftotal}} 或 {{$waftotal->waftotal}} 给我错误，因为试图获取非对象的属性。

建议将不胜感激。

Answer 1

first()将返回一个对象，因此您需要添加每个对象的属性： https://laravel.com/docs/5.6/eloquent#retrieving-single-models

而不是$total = $total_shirt + $total_pant + $total_tie;

这将是$total = $total_shirt->total_shirt + $total_pant->total_pant + $total_tie->total_tie;，因为您已将每个对象的总和分配给以该对象命名的属性。

但是您的第一个组合查询应该可以正常工作。你得到什么错误？

Answer 2

$maintotal = DB::table('carts')
->join('products', 'carts.productid', '=', 'products.id')
->select(DB::raw('sum(products.price*carts.quantity) AS maintotal3'))
->where('carts.userid', '=', $useri)->get();

@foreach($maintotal as $t)
{{$t->maintotal3}}
@endforeach