I was considering this approach or left right tree. I decided to use this, because it is easer to move or remove hole category with subcategories. What other approach do you suggest to use??
I know I can use composite pattern and then use “with” recurrently to get all sub categories, but isn’t it more complex? With many subcategories it would have to add a lot of queries, and still it do not solves problem with number of items in all subcategories.
I already have ordering (i have defined paths), but i want to use relations array to define items count. when i use relations array i have to specify foreign key, so i can’t count products which are under current category and all subcategories, because it uses this foreign key. I know i can archive this by public function in category model class, but if it is possible with relations array i would prefer to use it.
In such situations I prefer to hold a special field in the categories table (e.g. "itemCount").
When inserting or deleting items you’ll have to update counters of all ancestors, but it’s not that hard operation as counting items for all categories every time they are being retrieved (especially in your case, when you have to search a substring).