0

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)?

Karl Hill
  • 12,937
  • 5
  • 58
  • 95
0x7357
  • 13
  • 6

2 Answers2

0

You can use hasManyThrough relation. You check from this link

Add this code to your Category model:

public function products(){
    return $this->hasManyThrough(
        Product::class,
        Category::class,  
        "parent_id", 
        "category_id", 
        "id", 
        "id"
    );
}

Maybe, I am wrong with your foreign and primary keys. Please check it.

Maksat
  • 311
  • 5
  • 24
0

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).

Andrew Warren-Love
  • 16
  • Currently, I have to say that it looks a bit too complicated to me at first glance. There is actually no "depth". There are main categories and they have sub categories. Sub categories have no sub categories. I'm just thinking if it wouldn't be smarter to give the product a "base_category_id" and a "category_id". – 0x7357 Jul 01 '21 at 14:16
  • Yes, if you only have one level, this is too complicated. This solution was built for a hierarchy tree, not a single level. It would work for one level, but it is more than you need now. You would need to make sure it keep things updated if the category/subcategory relationships get changed, but that would make querying for matching categories or subcategories much easier. – Andrew Warren-Love Jul 02 '21 at 13:04
  • I have no idea how to mark your answer as "The Answer", but I actually use Closure Tables now. I read through some super documentation on Closure Tables and it illustrated it all very well. My basic thought was wrong. It's not the product that's in a Closure Table, it's the product category. The product itself only has a `category_id`. Thanks a lot for the push! :-) – 0x7357 Jul 06 '21 at 16:34