[SOLVED] Datamapper - Count related items

[eluser]drakeonfire[/eluser]
Hello WanWizard,

Thanks for the reply - I think it's what I was looking for, however, now I've implemented it, it doesn't seem to allow me to access the normal results (e.g. Title etc). Any ideas?

The query looks like so:

Code:

SELECT (SELECT COUNT(*) AS count
FROM (`products`)
LEFT OUTER JOIN `categories_products`
categories_products ON `products`.`id` = `categories_products`.`product_id`
WHERE
categories_products.category_id = categories.id) AS product_count
FROM (`categories`)
ORDER BY
`categories`.`order` asc, `categories`.`title` asc

The code in the controller is:

Code:

$items = new Category();
$products = $items->product;
$products->select_func('COUNT', '*', 'count');
$products->where_related('category', 'id', '${parent}.id');
$items->select_subquery($products, 'product_count');
$data['items'] = $items->order_by('order asc, title asc')->get();

And I'm trying to access the stuff by doing a foreach loop and then accessing the title by something like $item->title; however that doesn't bring anything out, even though $item->product_count; shows a 0.

Any ideas?

Thanks