CODEWITHSUNDEEP
phplaravellaravel-5laravel-query-builder
User57
User57

Reputation: 2505

How to handle null value inside whereIn?

I have the following Query to be executed where $category_id & $industry_id came through ajax request as array. so sometimes $category_id & $industry_id could be null value. So how do avoid that line of statement when the array would be null? 

$products = DB::table('users')
               ->join('products','products.auth_id','users.id')
               ->Join('reviews','products.id','reviews.product_id')
               ->select('products.*','users.avatar',DB::raw('(sum(rating)/count(user_id)) as rating'))             
               ->orwhereIn('products.category_id', [$request->get('category_id')])   
               ->orwhereIn('products.industry_id',[$request->get('industry_id')])   
               ->where('products.status','=','1')           
               ->groupBy('reviews.product_id')   
               ->latest()              
               ->get();

Upvotes: 0

Views: 1228

Answers (2)

Dharmesh Rakholia
Dharmesh Rakholia

Reputation: 1238

Try this Query does this help.

$query = DB::table('users')
   ->join('products','products.auth_id','users.id')
   ->Join('reviews','products.id','reviews.product_id')
   ->select('products.*','users.avatar',DB::raw('(sum(rating)/count(user_id)) as rating'))             
   ->where('products.status','=','1')           
   ->groupBy('reviews.product_id')   
   ->latest();

if ($request->has('category_id')) {
    $query->orWhereIn('products.category_id', $request->get('category_id'));
}

if ($request->has('industry_id')) {
    $query->orWhereIn(''products.industry_id', $request->get('industry_id'));
}
$products = $query->get();

Upvotes: 1

ailok
ailok

Reputation: 56

Simplest solution

    $query = DB::table('users')
       ->join('products','products.auth_id','users.id')
       ->Join('reviews','products.id','reviews.product_id')
       ->select('products.*','users.avatar',DB::raw('(sum(rating)/count(user_id)) as rating'))             
       ->where('products.status','=','1')           
       ->groupBy('reviews.product_id')   
       ->latest();

    if ($categoryId = $request->get('category_id')) {
        $query->orwhere('products.category_id', $categoryId);
    }

    if ($industryId = $request->get('industry_id')) {
        $query->orwhere('products.category_id', $industryId);
    }

    $products = $query->get();

BTW - changed orWhereIn to orWhere as looks like it is one value anyway.

Upvotes: 0

Related Questions