How to filter products by attributes (color, size, etc.) in Laravel

Question

I am new to Laravel and want to filter out specific products.

I have two tables in my database, the first is the table products, and the second is the table attributes.

Product List

Schema::create('products', function (Blueprint $table) {
  $table->bigIncrements('id');
  $table->BigInteger('category_id')->unsigned()->nullable();
  $table->string('name');
  $table->string('code');
  $table->integer('status')->default(1);
  $table->integer('featured')->default(1);
  $table->string('image');
  $table->longText('short_description');
  $table->longText('long_description');
  $table->timestamps();
})

Product attribute table

Schema::create('product_attributes', function (Blueprint $table) {.
  $table->bigIncrements('id');
  $table->unsignedBigInteger('product_id');
  $table->string('sku');
  $table->string('size');
  $table->string('color');
  $table->string('price');
  $table->string('stock');
  $table->timestamps();
})

Relationships

Because I have multiple attributes for a single product

class Product extends Model
{
    use HasFactory;

    public function attributes()
    {
         return $this->hasmany('App\Models\ProductAttributes', 'product_id');
    }
}

My Blade Files

<form action="{{url('/product/filter')}}" method="post">
 @csrf
<input type="hidden"value="{{$slug}}"name="slug">

<div
  class="custom-control custom-checkbox d-flex align-items-center justify-content-between mb-3">
   <input name="color" onchange="javascript:this.form.submit();" type="radio" class="custom-control-input" id="black" value=" ;black"> <label class="custom-control-label" for="black">Black</label>
</div>

</form>

I have a function in my controller

public function shop()
{

$filter_products = Product::with('attributes')->where(['category_id' => $category->id, 'color' => $request->color]);

    return view('frontend.shop', compact('filter_products'));
}

I don't get any results after applying this function

Please guide me how to filter products based on specific size or color on frontend store page. and what code will be included in the store functionality.

Please reply to me, I will thank you very much

Answer 1

$brands         = Brand::orderBy('id','DESC')->get();
    $colors         = Color::orderBy('id','DESC')->get();
    $sizes          = Size::orderBy('id','DESC')->get();
    $weights        = Weight::orderBy('id','DESC')->get();
    $tags           = Tag::orderBy('id','DESC')->get();
    try{
        if (!empty($_GET['colors']) || !empty($_GET['brands']) || !empty($_GET['sizes']) || !empty($_GET['weights']) || !empty($_GET['tags'])) {
            $products = Product::where( function($query ){
                $query->when(!empty(request()->colors) ,function($subquery){
                    $subquery->whereHas('colors', function($subquery)  {
                        $subquery->whereIn('colors.id',request()->colors);
                    });
                })->when(!empty(request()->sizes) ,function($subquery){
                    $subquery->whereHas('sizes', function($subquery)  {
                        $subquery->whereIn('sizes.id',request()->sizes);
                    });
                })->when(!empty(request()->weights) ,function($subquery){
                    $subquery->whereHas('weights', function($subquery)  {
                        $subquery->whereIn('weights.id',request()->weights);
                    });
                })->when(!empty(request()->tags) ,function($subquery){
                    $subquery->whereHas('tags', function($subquery)  {
                        $subquery->whereIn('tags.id',request()->tags);
                    });
                })->when(!empty(request()->brands) ,function($subquery){
                    $subquery->whereHas('brand', function($subquery)  {
                        $subquery->whereIn('brand_id',request()->brands);
                    });
                })->when(!empty(request()->is_new) ,function($subquery){
                    $subquery->where('is_new',request()->is_new);
                })->when(!empty(request()->gender) ,function($subquery){
                    $subquery->where('gender', 'LIKE', "%" . request()->gender . "%");
                });
            })->paginate(10);
        
            
            return view('front.shop',compact('products','brands','colors','sizes','weights','tags'));
        } else {
            return  redirect()->route('products');
        }
        
    } catch (\Exception $e) {
        toastr()->error(trans('Some thing is wrong please try again'));
        return  redirect()->route('products');
    }

Answer 2

You need to filter by relationship, please check the documentation

https://laravel.com/docs/9 .x/eloquent-relationships#querying-relationship-existence

for example Use WhereHas

$filter_products = Product::with('attributes')
   ->where(['category_id' => $category->id])
   ->whereHas('attributes',function($query) use($request){
      $query->where(['color' => $request->color]);
   });

If applied nowhere in with, all properties will be returned

You can prevent this behavior using the same filter applied in whereHas

$filter_products = Product::with(['attributes'=>function($query) use($request){
        $query->where(['color' => $request->color]);
    }])
    ->where(['category_id' => $category->id])
    ->whereHas('attributes',function($query) use($request){
        $query->where(['color' => $request->color]);
    });