How to Retrieve Products of a Nested Category using Many to Many Relationship in Laravel and order the result

I want to retrieve products of the selected category, as well as sub-categories and I may even need to order them according to price or date. Please let me know what changes should be made to the controller!

I am using a Many to Many Relationship for the 2 tables: Categories and Products, and a category_product to relate both.

Example

(Not all categories have sub-categories)

Gifts

Books

Toys

  • Boys
  • Girls

Phone

  • Samsung
  • Nokia

If a user clicks Phone, all products of the category ‘Phone’, ‘Samsung’ or ‘Nokia’ should appear!

Database

Products: id, name, price, created_at
Categories: id, name, slug, parent_id, sorting_order, created_at
category_product: id, category_id, product_id

Code:

Category Model:

class Category extends Model
{
    public function products()
    {
        return $this->belongsToMany('App\Product');
    }

    public function parent()
    {
        return $this->belongsTo('App\Category', 'parent_id');
    }

    public function children()
    {
        return $this->hasMany('App\Category', 'parent_id');
    }
}

Product Model

class Product extends Model
{
    public function categories()
    {
        return $this->belongsToMany('App\Category');
    }
}
class ProductController extends Controller {

    public function index($slug, Request $request)
    {
        if ( ! isset($_GET['sortBy']))
        {
            $category = Category::where('slug', $slug)->with('products')->first();

            if ( ! $category)
            {
                abort(404, 'Page not found');
            }
        }
        else
        {
            $slug = $request->segments()[1];
            $products = Category::where('slug', $slug);
            switch ($request->sortBy)
            {
                case 'latest':
                    $category = $products->with(['products' => function ($q) {
                        $q->orderBy('created_at', 'desc');
                    }])->first();
                    break;
                case 'asc':
                    $category = $products->with(['products' => function ($q) {
                        $q->orderBy('price', 'asc');
                    }])->first();
                    break;
                case 'desc':
                    $category = $products->with(['products' => function ($q) {
                        $q->orderBy('price', 'desc');
                    }])->first();
                    break;
                default:
                    $category = $products->with('products')->first();
                    break;
            }
        }

        return view('products', compact('category'));
    }
}

View

<form id="sortProducts" class="form-inline" method="get">
   {{ csrf_field() }}
   <label for="sortBy">Sort By:</label>
   <select name="sortBy" id="sortBy">
      <option value="latest">Latest</option>
      <option value="asc">Price Low to Hight</option>
      <option value="desc">Price High to Low</option>
   </select>
</form>

@foreach($category->products as $product)
    <div class="product">
        <img border="0" src="{{Voyager::image($product->image)}}" alt="{{$product->name}}">
        <div class="product-name">{{$product->name}}</div>
        <div class="product-price">${{$product->price}}</div>
    </div>
@endforeach

I am using Laravel Version 6.2 along with Voyager Version 1.3.

This Post Has One Comment

  1. No Fault

    Solution 1 (Pure Laravel):
    Add these two methods to your Category model:

    public function descendants()
    {
    $collection = new \Illuminate\Support\Collection();

    foreach ($this->children as $child) {
    $collection->add($child);
    $collection = $collection->merge($child->descendants());
    }

    return $collection;
    }

    public function getRouteKeyName()
    {
    return ‘slug’;
    }
    And use it in your ProductController controller like so:

    class ProductController extends Controller
    {
    public function index(Request $request, Category $category)
    {
    $categories = $category->descendants()->add($category)->pluck(‘id’);

    $products = DB::table(‘category_product AS cp’)
    ->join(‘products’, ‘cp.product_id’, ‘=’, ‘products.id’)
    ->select(‘products.*’)
    ->whereIn(‘cp.category_id’, $categories)
    ->get();

    return view(‘products’, compact(‘category’, ‘products’));
    }
    }
    You can then output them in your view file:

    @forelse($products as $product)

    image) }}” alt=”{{ $product->name }}”>
    {{ $product->name }}
    ${{ $product->price }}

    @empty

    There are no products in this category.

    @endforelse
    Solution 2 (Using a package):
    First of all, install the package:

    composer require kalnoy/nestedset
    Replace parent_id column with $table->nestedSet(); in your categories table and related migration file:

    Schema::create(‘categories’, function (Blueprint $table) {
    $table->bigIncrements(‘id’);
    $table->string(‘name’);
    $table->string(‘slug’)->unique();
    $table->nestedSet();
    $table->timestamps();
    });
    Then, update your Category model like so:

    use Illuminate\Database\Eloquent\Model;
    use Kalnoy\Nestedset\NodeTrait;

    class Category extends Model
    {
    use NodeTrait;

    protected $fillable = [
    ‘name’,
    ‘slug’,
    ];

    public function products()
    {
    return $this->belongsToMany(‘App\Product’);
    }

    public function parent()
    {
    return $this->belongsTo(self::class, ‘parent_id’);
    }

    public function children()
    {
    return $this->hasMany(self::class, ‘parent_id’);
    }

    public function getRouteKeyName()
    {
    return ‘slug’;
    }
    }
    You can now use it in your controller:

    class ProductController extends Controller
    {
    public function index(Request $request, Category $category)
    {
    $categories = Category::descendantsAndSelf($category->id)->pluck(‘id’);

    $products = DB::table(‘category_product AS cp’)
    ->join(‘products’, ‘cp.product_id’, ‘=’, ‘products.id’)
    ->select(‘products.*’)
    ->whereIn(‘cp.category_id’, $categories)
    ->get();

    return view(‘products’, compact(‘category’, ‘products’));
    }
    }
    You can output as shown in Solution 1.

    Please note that I assumed you use {category} key in your route definition. (See Route Model Binding) For example:

    Route::get(‘/products/category/{category}’, ‘ProductController@index’);
    Read the documentation first to create, update and delete an item in a nested set

Leave a Reply