Laravel Sum Eloquent sum of multiple multiplications from two different tables

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?

Advertisement

Answer