[SOLVED] Datamapper - Count related items

[eluser]drakeonfire[/eluser]
Hello,

I'm trying to display all categories from my database, which I can do (like so):

Code:

$cats= new Category();
$data['cats'] = $cats->get_iterated();

However, these categories are related to products, and I want to know how I can count how many products are in each category (without having to loop through them to do the query).

I've read through the manual and done searching online, however, I can't find anything that is doing more than one object (as they all seem to supply it with an ID first).

Any ideas?

Thank you.

[eluser]WanWizard[/eluser]
You'll have to use a subquery. The manual has an example (counting bugs with a specific status per user).

[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

[eluser]drakeonfire[/eluser]
Ah - could it be it's only selecting from the products table? If so .. how do I ammend that o_O? As I want all the categories too.

Thanks.

[eluser]WanWizard[/eluser]
You don't select anything from 'categories'.

Datamapper will only add a "SELECT *" automatically is no SELECT clause is present, which is not the case here. So you'll have to add ->select('*')-> to your query.

[eluser]drakeonfire[/eluser]
Hello WanWizard,

Thank you, it's working perfectly now.