Kohana nested SELECT statment

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