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.