CODEWITHSUNDEEP
sqllaravel
user2197774
user2197774

Reputation: 501

Converting sql via Laravel Query Build

I would like to convert the following sql statement into a Laravel Query Build: 

SELECT 
  * 
FROM 
  view_orderline_manifest_data 
where 
  OrderID = 7 
  and (
    ProdCategoryParentID != 4 
    or ProdCategoryParentID IS NOT NULL
  )

Tried the following:

$orderlinedata=DB::table('view_orderline_data')
       ->select('ProdName','ProdID')->where('CustID',$CustID)
       ->where('OrderID',$OrderID)
       ->where('ProdCategoryParentID','!=' , 4)
       ->orWhereNull('ProdCategoryParentID')
       ->pluck('ProdName','ProdID')->all();

The problem is the following is being executed:

SELECT 
  * 
FROM 
  view_orderline_manifest_data 
where 
  OrderID = 7 
  and ProdCategoryParentID != 4 
  or ProdCategoryParentID IS NOT NULL

essentially the brackets () are not being applied.

Upvotes: 0

Views: 27

Answers (2)

Diego Barrera
Diego Barrera

Reputation: 111

To get the result you desire, you'd need to pass a closure to the second where in your code.

$orderlinedata=DB::table('view_orderline_data')
       ->select('ProdName','ProdID')->where('CustID',$CustID)
       ->where('OrderID',$OrderID)
       ->where(function ($query) {
           $query->where('ProdCategoryParentID','!=' , 4)
                 ->orWhereNull('ProdCategoryParentID');
       })
       ->pluck('ProdName','ProdID')->all();

Laravel documentation on Parameter Grouping with the query builder

I hope it helps!

Upvotes: 2

Rashed Hasan
Rashed Hasan

Reputation: 3751

$orderlinedata = ViewOrderlineData::where('CustID',$CustID)
                                  ->where('OrderID', $OrderID)
                                  ->where(function ($query){
                                          $query->where('ProdCategoryParentID', '!=', 4)
                                                  ->orWhereNull('ProdCategoryParentID');
                                      })->pluck('ProdName','ProdID');

Upvotes: 0

Related Questions