Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 2 of 2

Thread: aspdnsf_EntityMgr Modification

  1. #1
    chrismartz is offline Senior Member
    Join Date
    Apr 2010
    Posts
    339

    Default aspdnsf_EntityMgr Modification

    I am trying to add a field to the following query that outputs the most looked at product image so I can use it as my category picture. Below is my current modified query but if there aren't any products tied to the categoryID, it doesn't go to the sub category to look for the sku/imagefilenameoverride. The part highlighted in red is my change. Any suggestions to make it pull farther down or combine all sub category products and pull the most looked at one?

    Code:
        IF @EntityName = 'Category' BEGIN
            SELECT Entity.CategoryID EntityID, Entity.CategoryGUID EntityGuid, Name,ColWidth,Description,SEName,SEKeywords,SEDescription,SETitle,SENoScript,SEAltText,ParentCategoryID ParentEntityID,DisplayOrder,SortByLooks,XmlPackage,Published,ContentsBGColor,PageBGColor,GraphicsColor
    			   ,isnull(NumProducts, 0) NumObjects, PageSize, QuantityDiscountID, Summary, SkinID, TemplateName 
    			   , (SELECT TOP 1 
    					CASE WHEN isnull(CAST(Product.ImageFilenameOverride as varchar),'') = '' THEN Product.SKU
    						ELSE Product.ImageFilenameOverride END AS EntityImage
    					FROM Product 
    					INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID 
    					INNER JOIN Category ON ProductCategory.CategoryID = Category.CategoryID
    				  WHERE Category.CategoryID = Entity.CategoryID
    				  ORDER BY Product.Looks DESC) EntityImage
            FROM dbo.Category Entity with (NOLOCK)
              left join (SELECT pc.CategoryID, COUNT(pc.ProductID) AS NumProducts
                         FROM  dbo.ProductCategory pc with (nolock)
                             join [dbo].Product p with (nolock) on pc.ProductID = p.ProductID  and p.deleted=0 and p.published=1
                         GROUP BY pc.CategoryID
                        ) a on Entity.CategoryID = a.CategoryID
            WHERE Published >= @PublishedOnly and Deleted=0
            ORDER BY ParentCategoryID,DisplayOrder,Name
        END

  2. #2
    chrismartz is offline Senior Member
    Join Date
    Apr 2010
    Posts
    339

    Default

    It looks like making the following modification always outputs a value, but is this the best way to do it?

    Code:
    WHERE Category.CategoryID = Entity.CategoryID OR Category.ParentCategoryID = Entity.CategoryID