Laravel query builder addSelect whereRaw condition show error

Question

Here I would add $product_id condition in addselect query on whereRaw but its show error both error screenshots added.

$product_id condition is necessary for desire output. Is there any alt way to pass product_id condition on whereRaw

I don't know what happens here

public function viewOrder($product_id)
{
    // Get all product with variant with color with category with stock
    // DB::enableQueryLog();
    $data=DB::table('product_masters')
        ->join('product_varients','product_varients.product_id','=','product_masters.id')
        ->join('color_masters','color_masters.id','=','product_varients.color_id')
        ->join('product_category_masters','product_category_masters.id','=','product_masters.category_id')
        //->groupBy('product_masters.id')
        ->orderBy('product_masters.id')
        ->where('product_masters.id',$product_id)
        ->select('product_varients.id as product_varients_id',
                 'product_masters.name as product_name',
                 'product_masters.id as product_id',
                 'product_masters.unit_id as product_unit',
                 'color_masters.name as color_name',
                 'color_masters.id as color_id',
                 'color_masters.color_hexcode',
                 'color_masters.pattern_img',
                 'product_category_masters.name as product_category_name',
                 'product_category_masters.id as product_category_id',
        )
        ->addSelect(array('stock_quantity' => function($query)
        {
            $query->select(DB::raw('COUNT(`product_varients_stocks`.`id`) as `total_qty`'))
                  ->from('product_varients_stocks')
                  ->whereRaw('product_varients_stocks.varient_id = product_varients.id')
                  ->limit(1);
        },'next_quantity' => function($query1)
        {
            // $purchase_items=DB::table('purchases')
            $query1->select(DB::raw('SUM(`purchase_items`.`qty`) as `qty_sum`'))
                  ->from('purchases')
                  ->join('purchase_items','purchases.id','=','purchase_items.purchase_id')
                  ->whereRaw('purchases.po_status','1')
                  ->whereRaw('purchase_items.product_id ',$product_id) // Show error here
                  ->whereRaw('purchase_items.product_id = product_masters.id')
                  ->whereRaw('purchase_items.varient_id = product_varients.id')
                  ->limit(1);
        }))
        ->get();
        // $query = DB::getQueryLog();
        // // echo "<pre>";
        // info($query);
        return $data;

}

Error screenshot

score 2 · Accepted Answer · answered Mar 24 '21 at 04:42

2

you need to pass $product_id inside that function with use() function

},'next_quantity' => function($query1) use ($product_id) //--------------this use()
        {
            // $purchase_items=DB::table('purchases')
            $query1->select(DB::raw('SUM(`purchase_items`.`qty`) as `qty_sum`'))
                  ->from('purchases')
                  ->join('purchase_items','purchases.id','=','purchase_items.purchase_id')
                  ->whereRaw('purchases.po_status','1')
                  ->whereRaw('purchase_items.product_id ',$product_id) // Show error here
                  ->whereRaw('purchase_items.product_id = product_masters.id')
                  ->whereRaw('purchase_items.varient_id = product_varients.id')
                  ->limit(1);
        }))

ref link PHP use() function for scope?

answered Mar 24 '21 at 04:42

Kamlesh Paul

11,778
2
20
33

Now it's now showing an error but it take default product_id as 1 – Jignesh Patel Mar 24 '21 at 04:45
2

@JigneshPatel this is fix of that error which your mentioned – Kamlesh Paul Mar 24 '21 at 04:50
Yep it fixes the error but not getting and desire output, when I pass product_id = 2 or 3 its default take product id as 1 can you please help me in this – Jignesh Patel Mar 24 '21 at 04:52
@JigneshPatel you need to debug that i can't see any error in code – Kamlesh Paul Mar 24 '21 at 04:58
output query screen shot https://prnt.sc/10tzt7o so you can understand what happen here @kamlesh Paul – Jignesh Patel Mar 24 '21 at 05:02
1

@JigneshPatel It sounds like the value passed into the method is incorrect but we have no way of debugging this. You could put something like ```dd($product_id)``` at the top of the method to verify you are getting the correct value. You should perhaps mark this answer as correct and create a new question for your new issue. – Andrew Mar 24 '21 at 05:28

Laravel query builder addSelect whereRaw condition show error

1 Answers1