Contrary to this topic (https://www.nopcommerce.com/boards/t/12256/showing-products-from-sub-categories.aspx) I want not show all products from all categories when parent category is selected but in advanced search, when I select a category, I need to search in all child categories.
To do that, I modified the stored procedure [ProductLoadAllPaged] as below :
...
CREATE TABLE #DisplayOrderTmp
(
[Id] int IDENTITY (1, 1) NOT NULL,
[ProductId] int NOT NULL
)
--
; With Cte_Cat_Hie As (
SELECT ID
FROM dbo.Category
WHERE ID = Case When @CategoryId = 0 Then ID Else @CategoryId End
UNION ALL
SELECT Child.ID
FROM Category AS Child INNER JOIN Cte_Cat_Hie AS Parent
ON Child.ParentCategoryId = Parent.Id
)
--
INSERT INTO #DisplayOrderTmp ([ProductId])
SELECT p.Id
FROM Product p with (NOLOCK)
LEFT OUTER JOIN Product_Category_Mapping pcm with (NOLOCK) ON p.Id=pcm.ProductId
INNER JOIN Cte_Cat_Hie On Cte_Cat_Hie.Id = pcm.CategoryId
LEFT OUTER JOIN Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.Id=pmm.ProductId
LEFT OUTER JOIN Product_ProductTag_Mapping pptm with (NOLOCK) ON p.Id=pptm.Product_Id
LEFT OUTER JOIN ProductVariant pv with (NOLOCK) ON p.Id = pv.ProductId
--searching of the localized values
--comment the line below if you don't use it. It'll improve the performance
LEFT OUTER JOIN LocalizedProperty lp with (NOLOCK) ON p.Id = lp.EntityId AND lp.LanguageId = @LanguageId AND lp.LocaleKeyGroup = N'Product'
WHERE
(
(
-- @CategoryId IS NULL OR @CategoryId=0
-- OR (pcm.CategoryId =@CategoryId AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
-- )
-- AND (
@ManufacturerId IS NULL OR @ManufacturerId=0
...
Hope this helps someone.
