am trying to filter courses by single category or multi categories but it's not working.

     $courses = Course::when($r->catId, function ($query, $catId) {
            $category = CourseCategory::find($catId);
            $catChildrenIdsArray = $category->children()->pluck('id')->toArray();
            $catParentIdsArray = CourseCategory::whereIn('parent_id', $catChildrenIdsArray)->pluck('id')->toArray();
            return $query->whereIn('course_category_id', $catParentIdsArray);
        })
        
        ->when($r->level, function ($query, $level) {
            return $query->where('level', 'like', "%{$level}%");
        })
        
          // PROBLEM NOT WORKING
        ->when($r->category, function ($query, $category) {
                $query->when(is_array($category) && count($category) > 1, function ($query) use ($category) {
                  
                    $query->where(function ($q) use ($category) {
                        foreach ($category as $cat) {
                            return $q->orWhere('course_category_id', 'like', "%{$cat}%");
                        }
                    });
                }, function ($query, $category) {
                    return $query->where('course_category_id', 'like', "%{$category}%");
                });
                
            })
         // END OF PROBLEM


            ->when($r->discount, function ($query, $discount) {
                if ($discount == 1) {
                    return $query->where('discount', '!=', null);
                }
            })
            
            ->when($r->rate, function ($query, $rate) {
                if ($rate == 1) {
                    $itemsRated = DB::table('ratings')->select('course_id', DB::raw('AVG(stars_rated) as count'))
                        ->groupBy('course_id')->orderBy("count", 'desc')->get();
                    $courseIds = [];
                    foreach ($itemsRated as $item) {
                        array_push($courseIds, $item->course_id);
                    }
                    $fieldOrder = join(", ", $courseIds);
                    return $query->whereIn('id', $courseIds)->orderByRaw("FIELD(id, $fieldOrder)");
                }
            })
            
            ->when($r->price && in_array($r->price, ['more-expensive', 'less-expensive']), function ($query) use ($r) {
                return $query->orderBy('price', $r->price == 'less-expensive' ? 'asc' : 'desc');
            }, function ($query) {
                return $query->statusOn()->order()->orderBy('id');
            })->paginate(18)->appends($r->query());

Everything else is working discount filter or rating or level but i can't search single or multi categories its always search all categories in the blade am getting like this

<input name="category[]" type="checkbox" value="{{ $c->id }}">

Please can someone help me with this i'm trying to fix this two days... (

CodePudding user response：

I believe you need to use an orWhere clause instead of a where clause at the beginning of your when method's closure section.

Secondly, you're returning way too soon in your foreach loop. The return statement should be outside the loop.

                    $query->where(function ($q) use ($category) {
                        foreach ($category as $cat) {
                            return $q->orWhere('course_category_id', 'like', "%{$cat}%");
                        }
                    });

Thirdly, it doesn't make sense to pass the whole category[] request parameter array in the LIKE condition of the query.

                }, function ($query, $category) {
                    return $query->where('course_category_id', 'like', "%{$category}%");
                });

Solution

Hence the query for the category section could be:

// ... PROBLEM NOT WORKING

    ->when($r->has('category[]') && count($r->input('category[]')), function ($query) use ($r) {
        return $query->orWhere(function ($q) use ($r) {
            foreach ($r->input('category[]') as $cat) {
                $q->orWhere('course_category_id', 'like', "%{$cat}%");
            }
            return $q;
        });
    })
// .... END OF PROBLEM