Rank: Advanced Member
Groups: Authorized User, Developers
Joined: 5/30/2020(UTC)
Posts: 125
Thanks: 14 times
Was thanked: 3 time(s) in 3 post(s)
|
For the life of me, I cannot get the category product list to sort in the same order as in the order admin. I even tried creating a new "Default" sort order that eliminated the Featured flag and was only based on OrderBy and Name. This is the query that gets submitted to SQL Server: Code:SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.ModelNumber, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy
FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId)
WHERE PC.CatalogNodeTypeId = 1
AND P.ProductId IN (
SELECT DISTINCT P.ProductId
FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId)
WHERE P.StoreId = @p1
AND P.EnableGroups = 0
AND PC.CatalogNodeTypeId = 1
AND PC.CategoryId IN (
SELECT CategoryId
FROM ac_CategoryParents
WHERE ParentId = @p2
)
AND P.VisibilityId = 0
)
GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.ModelNumber, P.IsFeatured
ORDER BY OrderBy ASC, Name ASC
I think the problem is that the query is taking the minimum OrderBy value found for the product in the ac_CatalogNodes table instead of the OrderBy for the CategoryId being specified. This may be a problem caused by trying to generalize the query for reuse in the _productRepo.AdvancedSearch. This comparable query returns the correct results: Code:SELECT P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.ModelNumber, P.IsFeatured, N.OrderBy
FROM ac_Products P
INNER JOIN ac_CatalogNodes N ON P.ProductId = N.CatalogNodeId
WHERE P.StoreId = 1
AND P.EnableGroups = 0
AND N.CatalogNodeTypeId = 1
AND N.CategoryId = 20
AND P.VisibilityId = 0
ORDER BY N.OrderBy ASC, P.Name ASC
I'm not sure how to fix it without creating my own version of the CategoryGridPage action that does not rely on the AdvancedSearch?
|
