Alright, hello guys, I'm struggling for a while with this problem so I will need your help.
So here is the problem I have database with more than 6000 products and I wanted to get some specific categories and their sub categories and only the products associated with them.
Here is a sample array that I have made that stores the categories (the key is the chosen category ID and the array value is all of the sub categories ID's).
$products_categories = array(
3 => [
4, 5 , 6
],
10 => [
40, 30, 20
]
);
And that's my eloquent query to get all of the products with those categories.
$all_fetched_categories = array_merge(...$products_categories);
if (!empty($products_categories)) {
$products = Product::query();
$products = $products->with([
'media',
])
->active()
->top()
->whereHas('categories', function ($query) use ($all_fetched_categories) {
$query->whereIn('shop_category_id', $all_fetched_categories);
});
$products = $products->get();
}
So the question is, I will need only 5 products per main category. With the code that I provided I can't make logical limitation in the query.
Fetch categories using whereIn and then add products eager loading with take(5) filter. Like this
$total = 5;
$categories = Categories::with(['products' => function($query) use ($total){
$query->latest()->take($total);
},'products.media'])
->whereIn('shop_category_id', $all_fetched_categories)
->get()
Assuming you have categories id in $all_fetched_categories
You can't do it with database group by syntax. Database group by returns single item from each group.
You need to use eloquent collection method groupBy(). By this method you can filter groups by whatever conditions you want.