How to get products of a "main category" if a product is related to only one "subcategory"?

Question

How do I get all products of a “main category” if a product is related to only one “subcategory”? A product is only related to a sub-category, while a sub-category is always part of the main category. So I want to have all products in the main category. A query like the below would not work or return no products because no product is related to category #1.

Categories::where(['id' => 1])->products();

Models/Category.php

public function parent(): BelongsTo
{
    return $this->belongsTo(Category::class, 'parent_id');
}

public function children(): HasMany
{
    return $this->hasMany(Category::class, 'parent_id');
}

Models/Product.php

public function category(): BelongsTo
{
    return $this->belongsTo(Category::class);
}

What do I need to do/change to get all products of the main category (preferably without checking if the category with ID #1 is the main category)?

Answer 1

One common solution to this problem of querying a hierarchical data structure is a Closure Table. There are many discussions of this pattern online, so I won't attempt to restate fully, but the short summary is that you store every path between each object and all of its ancestors along with the depth between them. This gives you a table with columns (ancestor_id, descendant_id, depth), so you can join through that table to collect all objects linked to any of a given descendant's ancestors, or any ancestor's descendants.

Here is an example query for how this works in practice to query all descendants of a given ancestor category, possibly with some syntax issues because I don't have a real database to run this against.

SELECT products.* FROM products
  INNER JOIN category_closure ON products.category_id = category_closure.descendant_id
WHERE category_closure.ancestor_id = 1;

We currently use this solution for virtually the exact same problem, products assigned anywhere within a hierarchy of categories. However, we are doing this in a Doctrine project, so implementation details of our solution likely wouldn't help here.

There are existing libraries to make this easier in Laravel, such as https://github.com/franzose/ClosureTable (I can't vouch for quality, just found now in a search).