Nested query in laravel 5.5 - development

Nested query in laravel 5.5

I want to get my products info from AppServiceProvider

Logic

Category -> Subcategory -> Product

now I need to get products base on category id

note: you might think it’s strange but it’s not really, just imagine you want show products in category page from all subcategories of that category. Then you’ll need such thing like i do. getting products base on category_id while you only save subcategory_id in products table.

this is my loop in AppServiceProdiver:

View::composer('welcome', function ($view) {
          $categories = Category::join('admin_designs', 'admin_designs.category_id', '=', 'categories.id')->get();
          foreach($categories as $category){
            $subcategory = Subcategory::join('categories', 'categories.id', '=', 'subcategories.category_id')->first();
            $designs = Product::where('subcategory_id', $subcategory)->first();
          }
        $view->with('categories', $categories);
        });

result of that loop is:

{"id":2,"title":null,"slug":"laptop","image":"category-1516430091.jpg","imageAlt":null,"status_id":1,"meta_tags":"laptop,tags","meta_description":"laptop description","created_at":"2018-02-01 11:41:30","updated_at":"2018-02-01 11:41:30","category_id":1},

PS: have no idea what is that! is not product/ is not complete
category less title!…

anyone can help with fixing that query?

from Laravel Questions and Answers https://laravelquestions.com/php/nested-query-in-laravel-5-5/
via Lzo Media