How to display categories in which there is a product?

Question

MySQL

There are categories with nesting, when opening the main category, you need to display only those subcategories in which the product is located

Example of category tables

id | parent_id | title
1 | 0 | Parts
2 | 1 | Body parts
3 | 2 | Bumper
4 | 2 | Mirrors

Product

id table example | category_id | title
1 | 3 | New bumper
2 | 3 | Black bumper

It turns out that when you go to the category - `Spare parts` we open the category `Body parts` and when you go into it, we open only one category `Bumper`, tk. There are no products in the category `Mirrors`.
So far, there is only 1 option in my head, this is every time when opening the main category, iterate over all child categories and for each make a request for the presence of products with the given id under the category `(Prod::where('category_id', id)->count()) `
In fact, it will work, but what if there is more nesting of categories, but you need to find out at level 1 or if there are a lot of products. Maybe there are other options?

Answer 1

Once form a recursive array of categories with JOIN, attach to each category a key with the value of the number of goods, or true / false. Or even at the formation stage, simply do not add these categories to the array

Answer 2

Ignore? The good developer you are, however, is growing.
One of the results is an empty list . This is all that can be guessed from the line.