I have 3 tables:

homepage
    order- integer
    status - boolean
    product_category_id - integer

category
    name- string
    slug- slug
    parent_id- integer

product
    name- string
    category_id- integer

When user wish to have categories in homepage like this

I want to have 15 products from categories in homepage including products from subcategories in random order. So far what i have done is

 //  all categories to be posted in homepage.
        $hp_categories = HomepageCategory::asc()->active()->get();

        foreach ($hp_categories as $hp_cat) {
            // array declaration
            $cat_with_subcat_arr = [];
            // category
            $product_category = ProductCategory::where('id', $hp_cat->product_category_id)->first();            
            // push category's id to array
            array_push($cat_with_subcat_arr, $product_category->id);
            // subcategories if any.
            $product_category->childrenCategoriesIds($cat_with_subcat_arr);
            
            // product from categories and subcategories via whereIn.
            $products = Product::whereIN('product_category_id', $cat_with_subcat_arr)->inRandomOrder()->limit(15)->get();
                    
            dd($products);
        }
        return $hp_categories;

my output is like this

Note: I could not do it by eager loading. So, I did by simple logic but cannot pass data to view.

CodePudding user response：

in controller i did

//define a collection;
$hp_cats = collect();

foreach ($hp_categories as $key=>$hp_cat) {

    // array declaration
    $cat_with_subcat_arr = [];

    // category
    $product_category = ProductCategory::where('id', $hp_cat->product_category_id)->first();            
 
    // push category's id to array
            
    array_push($cat_with_subcat_arr, $product_category->id);
            
    // subcategories if any.
            
    $product_category->childrenCategoriesIds($cat_with_subcat_arr);
            
            
    // product from categories and subcategories via whereIn.
            
    $products = Product::whereIN('product_category_id', $cat_with_subcat_arr)
            ->inRandomOrder()
            ->limit(15)
            ->get();
            
            
    $hp_cats[$key] = $products;
        
}

the result

Note: I could not found answer so it may not be the best solution. any help is appriciated.