Number of products in a category

[donttryathome]

We would like to list the number of products in each category next to the left nav category listing.

Is there a way to auto-populate this number? We would like to avoid having to go in and update the skin template every time we add a new product to a category.

Example...

Categories:
Sample Category One (99)
Sample Category Two (28)
Sample Category Three (132)
Sample Category Four (17)

[MarkC]

Using rev.categories.xml.config:

There's a field named NumObjects within EntityHelpers that contains the number of products per entity.
To add it just:

<a href="{concat('c-',EntityID,'-',SEName,'.aspx')}">
                        <xsl:if test="EntityID = $CategoryID or descendant::Entity/EntityID = $CategoryID">
                            <xsl:attribute name="style">font-weight:bold</xsl:attribute>
                        </xsl:if>
						
                        <xsl:value-of select="$eName"/>						
						
						<!--output the number of products for this category-->
						<xsl:value-of select="concat(' (', NumObjects, ')')" />						
                    </a>

This field, along with other entityhelper values are cached upon site startup, so if you add another product, you'll have to reset cache for it to update accordingly.

[donttryathome]

We aren't using rev.categories.xml.config to populate our category navigation. Would it be possible to implement this modification directly into the skin file or is this only possible using the xml package?

[webopius]

Hmm, tricky.

One way to do this is to write a SQL Server Stored Procedure that takes a Category ID and returns the total number of products for this category and all the child categories of the category.

Then, write an XSLTExtension to call the procedure from within an XML Package.

Unless someone with more brain cells than me (most people!) can figure it out, the SQL procedure is not simple though. The way I think would work would be to write a proc using SQL Server Common Table Expressions. For example (not tested)...

WITH ChildCatsCTE AS (
  SELECT  RootID = CategoryID, CategoryID
  FROM    Category
  UNION ALL
  SELECT  cte.RootID, c.CategoryID
  FROM    ChildCatsCTE cte
  INNER JOIN Category c ON c.ParentCategoryID = cte.CategoryID
)
SELECT  cnt.Products
FROM    Category c
        INNER JOIN (
          SELECT  CategoryID = RootID, Products = Count(pe.ProductId)
          FROM    ChildCatsCTE
          JOIN ProductEntity pe on pe.EntityID = CategoryID and pe.EntityType = 'category'
          GROUP BY RootID
        ) cnt ON cnt.CategoryID = c.CategoryID
WHERE c.CategoryID = @CategoryID

Given a value for @CategoryID, I think this will return the total number of products in the category including all child categories.

If the above proc works, it would be pretty easy for a developer to wrap this up with some code and turn this into a useful XSLT extension module that would work on any ASPDotNetStorefront site.