You need to join two tables using a nested SELECT statement. However, Kohana's Database module doesn't really allow this query:
mysql> select forum_categories.*, (
-> select count(forum_data.id)
-> from forum_data
-> where forum_data.category_id = forum_categories.id and
-> forum_data.parent_id = '0'
-> ) as cat_count
-> from forum_categories;
+----+----------------+------------------------+-----------+
| id | title | description | cat_count |
+----+----------------+------------------------+-----------+
| 1 | Category One | category 1 description | 2 |
| 2 | Category Two | category 2 description | 2 |
| 3 | Category Three | category 3 description | 0 |
| 4 | Category Four | category 4 description | 0 |
| 5 | Category Five | category 5 description | 1 |
| 6 | Category Six | category 6 description | 0 |
+----+----------------+------------------------+-----------+
6 rows in set (0.01 sec)
Solution
Rewrite your query as a left outer join (or inner join, depending on your case):
mysql> select forum_categories.*, ifnull(forum_data.total, 0) as total -> from forum_categories -> left join ( -> select category_id, count(forum_data.id) as total -> from forum_data where parent_id = '0' -> group by category_id -> ) as forum_data -> on forum_data.category_id = forum_categories.id; +----+----------------+------------------------+-------+ | id | title | description | total | +----+----------------+------------------------+-------+ | 1 | Category One | category 1 description | 2 | | 2 | Category Two | category 2 description | 2 | | 3 | Category Three | category 3 description | 0 | | 4 | Category Four | category 4 description | 0 | | 5 | Category Five | category 5 description | 1 | | 6 | Category Six | category 6 description | 0 | +----+----------------+------------------------+-------+ 6 rows in set (0.01 sec)
Translating to Kohana, we now have:
$subquery = DB::select('category_id', array('COUNT("forum_data.id")', 'total'))
->from('forum_data')
->where('parent_id', '=', '0')
->group_by('category_id');
$query = DB::select('forum_categories.*', array('IFNULL("forum_data.total", 0)', 'total'))
->from('forum_categories')
->join(array($subquery, 'forum_data'), 'LEFT')
->on('forum_data.category_id', '=', 'forum_categories.id')
->as_object()
->execute();
For more info, check out the Subqueries section of Kohana's Query Builder documentation.
