Peter,
When I needed a list of categories with the number of sites in each, I
created a custom method to return the necessary aggregated data.
~AJL
www.AaronJLynch.com
www.InstantSpot.com
On Tue, 2006-10-03 at 12:34 +0100, Peter Bell wrote:
> Hello All,
>
> I'm not really sure this is something any ORM should do, but I have a
> question.
>
> 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)?
>
> FYI, the "old style" SQL (for MSSQL) would be something like:
>
> SELECT cat.categoryTitle, ( SELECT count(prod.productID) FROM tbl_Product
> prod WHERE prod.productCategoryID = cat.CategoryID ) AS productCount
> FROM tbl_Category cat
>
> Any thoughts appreciated.
>
> Best Wishes,
> Peter
>
>
>
>
>
>
>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> --
> Reactor for ColdFusion Mailing List
> [email protected]
> Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> --
>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
