Show products including those in sub categories

01-19-2011, 07:48 AM

I'm using ML 8.0.1.2/8.0.1.2.

When viewing the products inside a category, I want to show the products in each sub category too, including sub-subcategories and so on. Some categories are up to 6 levels deep.

How do I achieve this?

01-24-2011, 12:12 PM

If any one got this please tell us, i am interested to know how we can achieve it.

show subcategories in grids and the products under subcategory in a list just under the subcategory Name.

thank you

02-14-2011, 06:57 AM

Has anyone done this without writing recursive stored procedures?

11-18-2011, 03:55 AM

I would like to know how to do this as well..

I am able to display the sub categories by editing the example package. But how do i display the products?

Code:

<?xml version="1.0" standalone="yes" ?>
<!-- ###################################################################################################### -->
<!-- Copyright AspDotNetStorefront.com, 1995-2011.  All Rights Reserved.					                -->
<!-- http://www.aspdotnetstorefront.com														                -->
<!-- For details on this license please visit  the product homepage at the URL above.		                -->
<!-- THE ABOVE NOTICE MUST REMAIN INTACT.                                                                   -->
<!-- ###################################################################################################### -->
<package version="2.1" displayname="Categories" debug="false" includeentityhelper="true">
  <PackageTransform>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
      <xsl:output method="html" omit-xml-declaration="yes"/>

      <xsl:param name="CategoryID">
        <xsl:choose>
          <xsl:when test="/root/System/PageName = 'showmanufacturer.aspx' or /root/System/PageName = 'showsection.aspx' or /root/System/PageName = 'showdistributor.aspx' or /root/System/PageName = 'showvector.aspx' or /root/System/PageName = 'showgenre.aspx'">0</xsl:when>
          <xsl:when test="/root/System/PageName = 'showcategory.aspx' and boolean(/root/QueryString/categoryid)">
            <xsl:value-of select="/root/QueryString/categoryid"/>
          </xsl:when>
          <xsl:when test="(/root/System/PageName = 'showcategory.aspx' or /root/System/PageName = 'showproduct.aspx') and boolean(/root/Cookies/LastViewedEntityInstanceID) and /root/Cookies/LastViewedEntityName = 'Category'">
            <xsl:value-of select="/root/Cookies/LastViewedEntityInstanceID"/>
          </xsl:when>
          <xsl:otherwise>0</xsl:otherwise>
        </xsl:choose>
      </xsl:param>

      <xsl:param name="AncestorID">
        <xsl:for-each select="/root/EntityHelpers/Category//Entity[EntityID = $CategoryID]">
          <xsl:value-of select="ancestor::*/EntityID"/>
        </xsl:for-each>
      </xsl:param>

      <xsl:param name="ParentID">
        <xsl:for-each select="/root/EntityHelpers/Category//Entity[EntityID = $CategoryID]">
          <xsl:value-of select="parent::*/EntityID"/>
        </xsl:for-each>
      </xsl:param>


      <xsl:template match="/">
        <xsl:element name="ul">
          <xsl:attribute name="class">
            <![CDATA[tame]]>
          </xsl:attribute>

          <xsl:apply-templates select="/root/EntityHelpers/Category/Entity">
            <xsl:with-param name="prefix" select="''"/>
          </xsl:apply-templates>

        </xsl:element>
      </xsl:template>

      <xsl:template match="Entity">
        <xsl:param name="prefix"></xsl:param>
        <xsl:param name="eName" select="aspdnsf:GetMLValue(Name)" />


        <li class="tame">
          <xsl:value-of select="$prefix" />
          <xsl:if test="number(ParentEntityID) != 0">
            <span class="catMark">&gt;&gt;</span>&#160;
          </xsl:if>
          <a href="{aspdnsf:EntityLink(EntityID, SEName, 'Category', 0)}">
            <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"/>
          </a>


          <!-- <xsl:if test="count(child::Entity)&gt;0 and (EntityID = $CategoryID or descendant::Entity/EntityID = $CategoryID)"> -->
          <xsl:if test="count(child::Entity)&gt;0">
            <ul class="tame">
              <xsl:apply-templates select="Entity">
                <xsl:with-param name="prefix" select="concat($prefix, '&#160;&#0160;')"/>
              </xsl:apply-templates>
            </ul>
          </xsl:if>
        </li>
      </xsl:template>

    </xsl:stylesheet>
  </PackageTransform>
</package>

12-05-2011, 07:54 PM

Looking for this as well, please advise if solution found.

12-06-2011, 01:01 AM

Here's a bit of SQL to start you off.

Code:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)

SELECT ParentCategoryID, c.CategoryID, Sort, p.ProductID, p.Name
FROM Categories c (NOLOCK)
LEFT JOIN ProductCategory pc (NOLOCK) ON pc.CategoryID = c.CategoryID
JOIN Product p (NOLOCK) ON p.ProductID = pc.ProductID
WHERE ParentCategoryID=96
ORDER BY 3,5

I've hardcoded 96 as a starting CategoryID, but you can pass a parameter to it. Or if you remove that WHERE clause then you'll get everything.

12-19-2011, 02:20 AM

Originally Posted by esedirect

Here's a bit of SQL to start you off.

Code:

WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
)

SELECT ParentCategoryID, c.CategoryID, Sort, p.ProductID, p.Name
FROM Categories c (NOLOCK)
LEFT JOIN ProductCategory pc (NOLOCK) ON pc.CategoryID = c.CategoryID
JOIN Product p (NOLOCK) ON p.ProductID = pc.ProductID
WHERE ParentCategoryID=96
ORDER BY 3,5

I've hardcoded 96 as a starting CategoryID, but you can pass a parameter to it. Or if you remove that WHERE clause then you'll get everything.

Thanks for this. But where does one place the above query?
I would need to create a store procedure right? then call that from the XML package?

Please can you advise me?

Regards
PD24

12-19-2011, 02:24 AM

I haven't tried it in a package itself as the SQL query myself. So you should give it a go. If it doesn't work there then, yes, move it to a stored procedure.

12-19-2011, 02:29 AM

Originally Posted by esedirect

I haven't tried it in a package itself as the SQL query myself. So you should give it a go. If it doesn't work there then, yes, move it to a stored procedure.

Im really struggling with this.. You are like my only life line on this forum..

If i run the query in SQL management it should bring up every category and every product.. But then i need to utilize the XML categories package so i can create an UL menu.

Basically im trying to fly out from categories, sub category and/or product.

Like this:
http://jsfiddle.net/FmNQq/4/

12-19-2011, 02:42 AM

I am having a go now, I was hoping you could keep on eye on this post so that i can have a little guidance. ??

Ill post up in the next few hours how i get on...

12-19-2011, 06:14 AM

I have managed to display all the products via the following xml package:

Code:

<?xml version="1.0" standalone="yes" ?>
<package version="2.1" displayname="Simple Product" debug="true" includeentityhelper="false">

    <!-- ###################################################################################################### -->
    <!-- Copyright AspDotNetStorefront.com, 1995-2011.  All Rights Reserved.					                -->
    <!-- http://www.aspdotnetstorefront.com														                -->
    <!-- For details on this license please visit  the product homepage at the URL above.		                -->
    <!-- THE ABOVE NOTICE MUST REMAIN INTACT.                                                                   -->
    <!--                                                                                                        -->
    <!-- ###################################################################################################### -->

    <query name="Products" rowElementName="Product">
        <sql>
            <![CDATA[
                WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
                    (
                    SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
                    FROM Category AS c
                    WHERE ParentCategoryID = 0
                    AND Deleted=0
                    AND Published=1
                    UNION ALL
                    SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
                    FROM Category AS c
                    INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
                    WHERE Deleted=0
                    AND Published=1
                    )

                    SELECT ParentCategoryID, c.CategoryID, Sort, p.ProductID, p.Name, p.SEName
                    FROM Categories c (NOLOCK)
                    LEFT JOIN ProductCategory pc (NOLOCK) ON pc.CategoryID = c.CategoryID
                    JOIN Product p (NOLOCK) ON p.ProductID = pc.ProductID

                    ORDER BY 3,5
            ]]>
        </sql>

    </query>

    <PackageTransform>
        <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
            <xsl:output method="html" omit-xml-declaration="yes" />

            <xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />
            <xsl:param name="WebConfigLocaleSetting" select="/root/Runtime/WebConfigLocaleSetting" />
            <xsl:param name="XmlPackageName" select="/root/System/XmlPackageName" />
            <xsl:param name="SecID">
                <xsl:choose>
                    <xsl:when test="count(/root/QueryString/sectionid) &gt; 0">
                        <xsl:value-of select="/root/QueryString/sectionid" />
                    </xsl:when>
                    <xsl:otherwise>0</xsl:otherwise>
                </xsl:choose>
            </xsl:param>

            <xsl:param name="CategoryID">
                <xsl:choose>
                    <xsl:when test="/root/System/PageName = 'showmanufacturer.aspx' or /root/System/PageName = 'showsection.aspx' or /root/System/PageName = 'showdistributor.aspx' or /root/System/PageName = 'showvector.aspx' or /root/System/PageName = 'showgenre.aspx'">0</xsl:when>
                    <xsl:when test="/root/System/PageName = 'showcategory.aspx' and boolean(/root/QueryString/categoryid)">
                        <xsl:value-of select="/root/QueryString/categoryid"/>
                    </xsl:when>
                    <xsl:when test="(/root/System/PageName = 'showcategory.aspx' or /root/System/PageName = 'showproduct.aspx') and boolean(/root/Cookies/LastViewedEntityInstanceID) and /root/Cookies/LastViewedEntityName = 'Category'">
                        <xsl:value-of select="/root/Cookies/LastViewedEntityInstanceID"/>
                    </xsl:when>
                    <xsl:otherwise>0</xsl:otherwise>
                </xsl:choose>
            </xsl:param>

            <xsl:param name="AncestorID">
                <xsl:for-each select="/root/EntityHelpers/Category//Entity[EntityID = $CategoryID]">
                    <xsl:value-of select="ancestor::*/EntityID"/>
                </xsl:for-each>
            </xsl:param>

            <xsl:param name="ParentID">
                <xsl:for-each select="/root/EntityHelpers/Category//Entity[EntityID = $CategoryID]">
                    <xsl:value-of select="parent::*/EntityID"/>
                </xsl:for-each>
            </xsl:param>

            <xsl:param name="CatID">
                <xsl:choose>
                    <xsl:when test="count(/root/QueryString/categoryid) &gt; 0">
                        <xsl:value-of select="/root/QueryString/categoryid" />
                    </xsl:when>
                    <xsl:otherwise>0</xsl:otherwise>
                </xsl:choose>
            </xsl:param>

            <xsl:template match="/">
                <xsl:element name="ul">
                    <xsl:attribute name="id">
                        <![CDATA[accordion3]]>
                    </xsl:attribute>
                    <xsl:apply-templates select="/root/Products/Product">
                        <xsl:with-param name="prefix" select="''"/>
                    </xsl:apply-templates>
                </xsl:element>
            </xsl:template>

            <xsl:template match="Product">
                <xsl:param name="prefix"></xsl:param>
                <xsl:param name="eName" select="aspdnsf:GetMLValue(Name)" />
                <li >
                    <!--<xsl:value-of select="$prefix" />-->
                    <xsl:if test="number(ParentEntityID) != 0">
                        <!--<span class="catMark">&gt;&gt;</span>&#160;-->

                        <a href="{concat('p-',ProductID,'-',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"/>
                        </a>



                    </xsl:if>
                    <xsl:if test="number(ParentEntityID) = 0">
                        <div class="menuText">
                            <xsl:value-of select="$eName"/>
                        </div>
                    </xsl:if>
                    <xsl:if test="count(child::Product)&gt;0">
                        <ul >
                            <xsl:apply-templates select="Product">
                                <xsl:with-param name="prefix" select="concat($prefix, '&#160;&#0160;')"/>
                            </xsl:apply-templates>
                        </ul>
                    </xsl:if>
                </li>
            </xsl:template>


        </xsl:stylesheet>
    </PackageTransform>
</package>

But i need to display the categories so that i can style the list with UL's.. Right now its just a massive list of product links. Any ideas on making it show up products under the categories.???

Thread: Show products including those in sub categories

Show products including those in sub categories

interested to know about it.