Laravel Eloquent in many to many relation limit relational data

Question

I have many to many relations. Product, Category and category_product as pivot. I want to fetch Product through Category but I need each Category 10 Product.

here is my query

 Category::with(['grocery_product')->get();

Category model :

   public function grocery_product(): BelongsToMany
   {
       return $this->belongsToMany(Product::class)->limit(10);
   }

but here limit is not working. if I remove limit(10) then I get all products associated with that Category.

Note: I have tried with take(10) function also.

How to fetch each category's 10 products?

Answer 1

By Default laravel doesn't have any out of the Box solution for the scenario.

So you can use the eloquent-eager-limit by staudenmeir

You can do something like this.

//Category Model
use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
  public function grocery_product(): BelongsToMany
{
    return $this->belongsToMany(Product::class);
}

Inside Product model use the same trait

//Product Model
use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;

In Controller.

//to get latest 5 `grocery_product` on each category
$fiveGroceryOfEachProduct = Category::with(['grocery_product' => function ($query) {
    $query->latest()->limit(5);
}])->get();

Calling limit method without using this pacakage will apply the limti on overall query. Not on each Model.

You can view the github issue here

Edit for Query Compatibility issue

So as the pacakage suggests. Please set

'strict' => false, 

In your config/database.php file, set 'strict' => false, for the MariaDB/MySql connection. And make sure to restart your dev server