I want to return a list of categories with the number of products in each,
but I might have 100 categories, so I'm looking for a way to get
CategoryTitle and ProductCount for n-categories using a single query. Does
Reactor happen to have any such "aggregate" support? I couldn't find
anything, it really isn't something I've seen in ORMs, but it is a use case
that (for some reason) comes up time and time again for me.
Any starting points or would I just use the query object to compose my own
custom SQL (and does it support subqueries in a select statement)?
I would think that this is probably something that you'd do a custom method for. Possibly off of the Category Gateway?
You could set it up so that if you pass it a categoryid then it will only give you the product count for that category, otherwise it returns a complete list of categories and their product count.
I think this will do what you need without the subquery....
SELECT cat.CategoryID, cat.categoryTitle, count(prod.productID) AS ProductCount
FROM tbl_Category cat
LEFT JOIN tbl_Product prod ON cat.CategoryID = prod.productCategoryID
GROUP BY cat.CategoryID
ORDER BY cat.CategoryID
Stephen
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
