Level 74
The easiest would be to add a parent category column to your table, that ways you can group them.
May 9, 2024
8
Level 1
How to count the number of related posts to a category tree?
I'm trying to count the number of products in each category. I do recursion and write thousands of lines of code. I think I'm doing something wrong)))
Can you tell me how to correctly implement this calculation? There is a tree of categories, the top categories have parent_id = null, all child categories have a parent_id that matches the parent's "category_id" string.
Example:
Clothes (quantity not specified) //need 26
Jackets and windbreakers (quantity not specified) //need 26 (23+3)
Jackets (5) //need 23 (5+10+8)
Long sleeve jackets (10) // Ok 10 - no child categories
Short sleeve jackets (8) // Ok 8 - no child categories
Windbreakers (3) // Ok 3 - no child categories
Shoes (quantity not specified)
...
You need to somehow go through the collection and sum up the number of products of all child categories.
public function recount()
{
$categories = Category::select('id', 'category_id', 'parent_id')->withCount('products')->get();
dd($categories);
}
I get the collection like this:
Illuminate\Database\Eloquent\Collection {#1660 ▼
#items: array:51 [▼
0 => App\Models\Category {#1659 ▼
...
#attributes: array:4 [▼
"id" => 30
"category_id" => 1
"parent_id" => null
"products_count" => 0
]
...
}
1 => App\Models\Category {#1658 ▼
...
#attributes: array:4 [▼
"id" => 31
"category_id" => 2
"parent_id" => null
"products_count" => 0
]
...
}
2 => App\Models\Category {#1657 ▼
...
#attributes: array:4 [▼
"id" => 32
"category_id" => 21
"parent_id" => 2
"products_count" => 0
]
...
}
3 => App\Models\Category {#1656 ▼
...
#attributes: array:4 [▼
"id" => 33
"category_id" => 211
"parent_id" => 21
"products_count" => 0
]
...
}
4 => App\Models\Category {#1655 ▼
...
#attributes: array:4 [▼
"id" => 34
"category_id" => 212
"parent_id" => 21
"products_count" => 0
]
...
}
5 => App\Models\Category {#1654 ▼
...
#attributes: array:4 [▼
"id" => 36
"category_id" => 2111
"parent_id" => 211
"products_count" => 15
]
...
}
Level 1
@Tray2 Do not quite understand. It seems like he exists.
Schema::create('categories', function (Blueprint $table) {
$table->id();
$table->integer('category_id')->nullable();
$table->integer('parent_id')->nullable();
$table->string('name', 100);
$table->integer('total_products')->default(0);
$table->timestamps();
$table->softDeletes();
});
Level 74
@Rediska You can't select the id, you need to use the parent id, otherwise you will get every category
Level 1
@Tray2 I did not get anything :c
Level 74
@Rediska Am I supposed to guess what you tried?
Level 1
@Tray2 I'll write what I tried to do, even though I'm ashamed to do it))) You can laugh)) At least it works) Almost works. We need to get rid of duplicates.
With this code, I first tried to find all child categories of each category. And at the second stage I would count the number of products from the "child" array.
public function recursiveChild($tree, $parent)
{
foreach($tree as $id => $item) {
if ($id == $parent) {
foreach ($tree[$id]['childs'] as $child) {
$number = $tree[$id]['parent_id'];
$tree = self::recursiveParent($tree, $number, $child);
$tree = self::recursiveChild($tree, $child);
}
}
}
return $tree;
}
public function recursiveParent ($tree, $number, $child)
{
foreach($tree as $id => $item) {
if (isset($tree[$number]['parent_id']) && $tree[$number]['parent_id'] != null) {
$num = $tree[$number]['parent_id'];
$tree[$num]['childs'][] = $child;
$tree = self::recursiveParent($tree, $tree[$num]['parent_id'], $child);
}
}
return $tree;
}
public function recount()
{
$categories = Category::select('id', 'category_id', 'parent_id')->withCount('products')->get();
$tree = [];
foreach($categories as $item) {
$tree[$item->category_id]['id'] = $item->id;
$tree[$item->category_id]['category_id'] = $item->category_id;
$tree[$item->category_id]['parent_id'] = $item->parent_id;
$tree[$item->category_id]['count'] = $item->products_count;
$tree[$item->category_id]['childs'] = [];
}
foreach($tree as $id => $item) {
if ($item['parent_id'] !== null) {
$parent_id = $item['parent_id'];
$tree[$parent_id]['childs'][] = $id;
}
}
foreach ($tree as $id => $item) {
if (!empty($item['childs'])) {
foreach($item['childs'] as $child) {
$tree = self::recursiveChild($tree, $child);
}
}
}
dd($tree);
Level 74
@Rediska you should let the database handle that.
It will require some SQL knowledge but it's doable.
Level 1
Looks like a nested tree problem to me, @rediska. Explanation: falsinsoft.blogspot.com/2013/01/tree-in-sql-database-nested-set-model.html
A quick look up brought me several related links/packages:
- github.com/lazychaser/laravel-nestedset
- codecourse.com/articles/recursive-nested-data-in-laravel/
- vicklr.com/tree-hierarchies-in-laravel
(i am not allowed to post links)
Please or to participate in this conversation.