Recursive Statistical Query

Yii 1.1 General Discussion
1

Hello people,

I have two models Category and Company, a parent category can have many subcategories (which is also a category model) and a subcategory can have many Companies. I can get the number of companies under a subcategory using an itemCount statistical query in my relations, however, I also want this relation to work on parent categories. You can see my tables below;




CATEGORY TABLE


+----+--------------------+--------------------+

| id | name               | parent_category_id |

+----+--------------------+--------------------+

|  1 | Automotive         |               NULL |

|  2 | Agriculture        |               NULL |

|  3 | Education          |               NULL |

|  4 | Entertaintment     |               NULL |

|  5 | Financial          |               NULL |

|  6 | Healthcare         |               NULL |

|  7 | Media & Creative   |               NULL |

|  8 | Print Media        |                  7 |

|  9 | Internet Media     |                  7 |

| 10 | Photography & Film |                  7 |

| 11 | Hospitals          |                  6 |

| 12 | Dentists           |                  6 |

| 13 | Doctors            |                  6 |

| 14 | Therapy Services   |                  6 |

| 15 | Schools            |                  3 |

| 16 | Universities       |                  3 |

| 17 | Tutoring           |                  3 |

+----+--------------------+--------------------+







COMPANY TABLE


+----+---------------------------+-------------+

| id | title                     | category_id |

+----+---------------------------+-------------+

|  1 | Rocket Industries         |           8 |

|  2 | Amazon Co.                |           8 |

|  3 | Fast Company              |           8 |

|  4 | Warner Bros Industries    |           8 |

|  5 | Rich Industries           |           9 |

|  6 | Blank Organic             |          10 |

|  7 | Retro Ltd                 |          10 |

|  8 | 3M Company                |          11 |

|  9 | Muka Industries           |          11 |

| 10 | Performance Entertainment |          11 |

| 11 | The Java Company          |          13 |

+----+---------------------------+-------------+

The Category model has the following relations;




/**

 * @return array relational rules.

 */

public function relations()

{

	// NOTE: you may need to adjust the relation name and the related

	// class name for the relations automatically generated below.

	return array(

		'parentCategory' => array(self::BELONGS_TO, 'Category', 'parent_category_id'),

		'categories' => array(self::HAS_MANY, 'Category', 'parent_category_id', 'with' => 'itemCount'),

		'companies' => array(self::HAS_MANY, 'Company', 'category_id'),

		'itemCount' => array(self::STAT, 'Company', 'category_id')

	);

}

$category->itemCount (returns the count of Companies for subcategories)

If $category is a parent category, however, I want it to return the sum of company count under the subcategories, but I have no idea how this can be implemented.

Note: Only subcategories can have companies and no parent category can have a company.

2

Anyone? I thought this would be a common requirement.

3

Hi,

I don’t think it’s possible to get needed results using one stat relation only, thus I can suggest following two ways:

First - is to create custom function with your count query.

Second - is to sum itemCount results for categories, e.g.




$parent = Category::model()->with('categories')->findByPk(1);

$count = 0;

foreach($parent->categories as $cat){

      $cout += $cat->itemCount;

}

or sort of.

Hope it’ll help,

Yuga

4

Thanks for the suggestion. I think that is what I’m going to do since I cannot find a proper solution using relations.