how to add Limit() to pivot table

I have two tables products and categories and a pivot table category_product.

I have added relation in Product.php is below.

public function categories()
{
    return $this->belongsToMany(Category::class);
}

I have added relation in Category.php is below.

public function products()
{
    return $this->belongsToMany(Product::class);
}

Both are working fine.

But what I want is, I want to take all the categories which has products and the products should not return more than 6 records. Meaning I have a two categories Music, Movies and both have more than 50+ products. The code should be return only 6 products for both Music, Movies categories.

This is what I tried,

    Category::with(['products' => function ($query){
        return $query->select()->limit(6);
    }, 'products.properties'])
    ->whereHas('products', function ($query){
        return $query->select()->limit(6);
    })->when($slug, function($query) use($slug){
        $query->whereIn('slug', $slug);
    })->get();

and this,

 Category::addSelect([
               'products' => Product::select('id')->whereColumn('products.id', 'categories.id')->orderBy('id')->limit(6)
           ])->when($slug, function($query) use($slug){
              $query->whereIn('slug', $slug);
           })->get()

Second try returns error, "stripos() expects parameter 1 to be string, object given"

I am using laravel 6.

Thanks in advance, any answers and suggestion welcome.

Added My Answer Below

3 answers

  • answered 2019-09-12 05:51 Brinda Fadadu

    you can use like below.

    $cat = Category::with('products')->when($slug, function($query) use($slug){
                    $query->whereIn('slug', $slug);
                })->get();
    
                foreach ($cat as $key => $value) {
                    $value->products->first();
                }
    

  • answered 2019-09-12 06:33 Sandeep Sudhakaran

    I found a solution for my question. Please have a check and please advice me if any better way to solve it.(mainly I want to reduce the number of queries)

    The solution is

    public function getProductsBasedOnCategory(array $slug){
        $categories = Category::select()->when($slug, function($query) use($slug){
            $query->whereIn('slug', $slug);
        })->get();
        $res = new Collection();
        foreach ($categories as $category){
            $category->products = $category->products()->limit(6)->get();
            $res->push($category);
        }
        return $res;
    }
    

    This function executing queries how many categories I have.

    If I have 10 categories, its executing 10 queries. Exclude $categories query

    Is this answer is good or you have any better solution. I want to reduce the number of queries execution.

    Please suggest.

  • answered 2019-09-12 06:37 ashish bansal

    please use this. Also add all your logics to eloquent, not in controller.

    public function categories()
    {
       return $this->belongsToMany(Category::class)->limit(6);
    }