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.
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 :
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?
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();