How to properly store product categories for products?

Hi everybody! Can you tell me how to properly store product categories for products? There is a table of categories:

$table->id();
$table->integer('parent_id')->nullable();
$table->string('title', 100);

Categories have parent and child. For example:

id-----parent_id-----name

1-------null----------shoes

2--------1------sneakers and slippers

3--------2----------slippers

4--------2---------sneakers

There is also a product table:

$table->id();
$table->string('title', 100);

If I specify the id of the final category directly (for example, "slippers"), then I run into a problem: When displaying products from the main category ("Shoes"), I need to go through the array of categories, collect all the ids of the parent categories and pass the array of category ids in the request. I also tried to go through the category cycle at the time of adding the product and write the IDs of all categories into an intermediate table (category_product) using "belongsToMany". In general, I do not understand how to implement it correctly. There will be about 3 thousand categories. And there are several million goods. Please tell me.

LaryAI

3 years ago

Level 58

One solution could be to use a many-to-many relationship between the products and categories tables. This way, a product can belong to multiple categories and a category can have multiple products.

To implement this, you can create a pivot table called "category_product" with columns "category_id" and "product_id". Then, in your Product model, define the relationship like this:

public function categories()
{
    return $this->belongsToMany(Category::class);
}

And in your Category model:

public function products()
{
    return $this->belongsToMany(Product::class);
}

To add a product to a category, you can simply call the attach method on the categories relationship:

$product = Product::find(1);
$category = Category::find(2);

$product->categories()->attach($category);

To retrieve all products in a category, you can use the products relationship on the category:

$category = Category::find(2);
$products = $category->products;

This way, you don't need to traverse the category hierarchy to get all products in a category.

Snapey

3 years ago

Level 122

If you want to find the products that are in categories that are children of a specific category then you just need the IDs of the child categories. How complex this is depends on whether you have a limit on the amount of nested categories.

In the simple case of shoes with a sub category of slippers, you would pass in the ID of shoes then;

$maincat = Category::find($id)->id;
$subcat = Category::where('parent_id', $maincat)->get('id');

$products = Product::whereIn('category_id', $subcat + [$maincat])->get();

Rediska

3 years ago

Level 1

@Snapey That's exactly what my question is =) There can be more nested categories. For example 5: (Clothes, shoes and accessories -> accessories -> bags and backpacks -> bags -> laptop bags)

Did I understand you correctly? In the intermediate table (category_product), where I will store several rows with product ids and all ids of parent categories, does it make sense at all? I must forget this option. I need to use an extra column in the product table - category_id. I must first get an array of parent categories, then pass this array of category IDs to the request. All right?

Snapey

3 years ago

Level 122

@Rediska I don't think it makes any sense to store the complete category hierarchy against each product. I would just store its category and then workout what categories you want to display at any time.

It does mean that when gathering the applicable category IDs you need a recursive function to find all the children of a specified category.

Laravel Daily has a relevant tutorial : https://laraveldaily.com/post/eloquent-recursive-hasmany-relationship-with-unlimited-subcategories

1 like

Please or to participate in this conversation.