Question about Relational Active Record and STAT

I have categories and items categories looks like this:

id_category

id_parent

name

path // it uses IP notation like 001.003.001 for ordering.

items look like this

id_item

id_category

name

description

path //it is path of category

i want to count items in categories using (it is part of relations array):

//…

‘itemCount’ => array(self::STAT, ‘Item’, ‘id_category’,‘condition’=>‘path LIKE ‘.$this->tableName().’.path%’),

//…

but for example

category with path .001. should count all items with contain .001. at begging of the path for example:

.001.

.001.001

.001.002

.001.002.001

etc.

How can i do that??

bad approach for ordering categories but you can achieve that type of ordering using LOCATE with CONCAT.

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

I wanted to create categories portlet which would than be cached, but maybe you are right. I tried composite pattern but when i made this relation

‘subcategories’ => array(self::HAS_MANY, get_class($this), ‘id_parent’,‘with’=>‘subcategories’,‘order’=>‘ordering’)

Mysql server couldn’t handle it. Although there is only 1 root category with 2 subcategories on second level and 3 on third.