How to get products category wise in Laravel?

user_10255904 0
Viewed 1213

A similar question was asked here.

I am working on APIs. My products table contains more than ten thousand products of different categories. I need to decrease query execution time, so for that purpose, I've to modify an API that will fetch only 20 products of each category and group the whole API response category-wise. The API response will be like the following.

{
"data": {
"products": {
  "Beauticians & Style": [
    {
      "Title": "Product A",
      "Price": "0.00",
      "DiscountPrice": 0
    },
    {
      "Title": "Product B",
      "Price": "0.00",
      "DiscountPrice": 0
    }
  ],
  "Groceries": [
    {
      "Title": "Product G",
      "Price": "0.00",
      "DiscountPrice": 0
    },
    {
      "Title": "Product R",
      "Price": "0.00",
      "DiscountPrice": 0
    },
    {
      "Title": "Product O",
      "Price": "0.00",
      "DiscountPrice": 0
    },
    {
      "Title": "Product C",
      "Price": "0.00",
      "DiscountPrice": 0
    }
  ],
  "Women's Fashion": [
    {
      "Title": "Product W",
      "Price": "0.00",
      "DiscountPrice": 0
    },
    {
      "Title": "Product O",
      "Price": "0.00",
      "DiscountPrice": 0
    },
    {
      "Title": "Product M",
      "Price": "0.00",
      "DiscountPrice": 0
    }
  ]
}
}

Controller

$products = Category::with('products', function($q){
    $q->take(20);
})->get();

Category Model

class Category extends Model
{
    use HasFactory;

    public function products()
    {
        return $this->hasMany('App\Models\Product', 'CategoryID', 'CategoryID');
    }
}

I've tried this but not getting the exact result.

laravel-5 laravel-8 php
2 Answers

When accessing Eloquent relationships as properties, the related models are "lazy loaded". This means the relationship data is not actually loaded until you first access the property.

The solution, you can use:

$products = Category::with('products')
    ->get()
    ->map(function ($query) {
        $query->setRelation('products', $query->products->take(20));
        return $query;
    });

Ref :

Update

OP comment :

But in this case we are fetcing all 10k products then set relation on collection

Yes, this is performance issue, but, your question is about eadger. This is limitation.

Eloquent as a tool, not a solution. It's just a wrapper on SQL. If you wanted to limit the number of products per category to (n), the query becomes complex.

To solve this issue you will need to use window function :

SELECT * 
FROM (
    SELECT
        *, 
        ROW_NUMBER() OVER (PARTITION BY `category_id`) AS row_num 
    FROM `products`
) AS temp_table 
WHERE row_num <= 10 
ORDER BY row_num

Alternatively, you can use this Laravel Eloquent extension to allow limiting the number of eager loading results per parent using window functions.

Category Model

use Staudenmeir\EloquentEagerLimit\HasEagerLimit;
use App\Models\Product;

class Category extends Model
{
    use HasEagerLimit;

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

Product Model

use Staudenmeir\EloquentEagerLimit\HasEagerLimit;

class Product extends Model
{
    use HasEagerLimit;
}

Try

$products = Category::with(['products' => function ($query) {
    $query->latest()->limit(20);
}])->get();

Does this explain enough to you?

user_9844220

if each product have only one category, your tables structure must be as below:

products
    id
    ...
    category_id
categories
    id
    ...

and your products relation in Category must be as below:

class Category extends Model
{
    use HasFactory;
    public function products()
    {
        return $this->hasMany(App\Models\Product::class);
    }
}

and change your controller to:

$products = Category::with('products', function($q){
            $q->take(20);
        })->get();
user_99834030
Related