Laravel Sum Eloquent sum of multiple multiplicatio…

Currently, in my Laravel project controller, I am using one query

QUERY 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();
    }

In above query, I am using two database tables as pricings and carts where I am calculating total bill price of shirt item by getting a price from pricing table and quantity from carts table.

Now I also want to add another item with a shirt like a pant, tie, etc. How do I pass more multiplication to the sum?

Please help me with the syntax. Can I do something like this

QUERY 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();

OR even if I calculate total separately for each item How do I add it?

$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_shirt + $total_pant + $total_tie; ?

To display value in view.blade.php I use something like {{$total->total}}

Thanks in advance.

TRIED :

$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();

but it gives me error as

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘ pricings.Bedding_Mattress_Duvet_Coverc’ at line 1 (SQL: select sum(pricings.Regular_Laundrycarts.q_Regular_Laundry, pricings.Bedding_Mattress_Duvet_Covercarts.q_Bedding_Mattress_Duvet_Cover, pricings.Bedding_Comforter_laundrycarts.q_Bedding_Comforter_laundry, pricings.Bedding_Blanket_Throwcarts.q_Bedding_Blanket_Throw, pricings.Bedding_Pillow_laundrycarts.q_Bedding_Pillow_laundry, pricings.Bath_Mat_laundrycarts.q_Bath_Mat_laundry, pricings.Every_Hang_Dry_Itemcarts.q_Every_Hang_Dry_Item ) AS waftotal from pricings inner join carts on carts.sp_id = pricings.sp_id where pricings.sp_id = 1 and carts.id = 23 limit 1)

Even if I write seperate queries

$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 ;

In view.blade.php {{$waftotal}} or {{$waftotal->waftotal}} gives me eerror as Trying to get property of non-object .

Suggestions will be appreciated.

Answer

first() will return an object, so you need to add the properties of each object: https://laravel.com/docs/5.6/eloquent#retrieving-single-models

Instead of $total = $total_shirt + $total_pant + $total_tie;

It would be $total = $total_shirt->total_shirt + $total_pant->total_pant + $total_tie->total_tie; since you have assigned the sum to each object to a property named after the object.

But your first combined query should work fine. What error do you get?

Laravel Sum Eloquent sum of multiple multiplications from two different tables

Advertisement

Answer