Converting sql via Laravel Query Build

Question

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.

Rwd · Accepted Answer · 2019-05-15 17:55:47Z

2

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!

edited May 15, 2019 at 17:55

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

answered May 15, 2019 at 17:50

Diego Barrera

1114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rashed Hasan · Accepted Answer · 2019-05-15 17:54:55Z

0

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

answered May 15, 2019 at 17:54

Rashed Hasan

3,77111 gold badges43 silver badges91 bronze badges

Collectives™ on Stack Overflow

Converting sql via Laravel Query Build

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related