logo
Welcome to our new AbleCommerce forums. As a guest, you may view the information here. To post to this forum, you must have a registered account with us, either as a new user evaluating AbleCommerce or an existing user of the application. For all questions related to the older version of Gold and earlier, please go to forums.ablecommerce.com. Please use your AbleCommerce username and password to Login. Forums Registration.

Notification

Icon
Error

Options
Go to last post Go to first unread
sweeperq1766875  
#1 Posted : Friday, June 18, 2021 11:14:53 AM(UTC)
sweeperq1766875

Rank: Newbie

Groups: Authorized User, Developers
Joined: 5/30/2020(UTC)
Posts: 7

Thanks: 8 times
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?

Wanna join the discussion?! Login to your AbleCommerce Forums forum account. Forums Registration.

sweeperq1766875  
#2 Posted : Friday, June 18, 2021 1:59:17 PM(UTC)
sweeperq1766875

Rank: Newbie

Groups: Authorized User, Developers
Joined: 5/30/2020(UTC)
Posts: 7

Thanks: 8 times
Okay, confirmed that the sorting is off because the category condition is never applied to the first join. I updated the CommerceBuilder source file /Search/Providers/SearchProviderBase.cs by adding the following code chunk to the AdvancedSearch method, right before the GROUP BY, then copied the new CommerceBuilder.dll to the /Lib directory:

Code:
if (categoryId != 0)
{
    selectQuery.Append(" AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = :categoryId)");
}


Joe Payne @ AbleMods LLC  
#3 Posted : Saturday, September 4, 2021 5:32:07 PM(UTC)
Joe Payne @ AbleMods LLC

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 489

Thanks: 82 times
Was thanked: 14 time(s) in 13 post(s)
Funny I found a different solution.

The change I made forces what I call a 'shallow' search i.e. the query won't go deeper than the specified category id. That works great, except if you're implementing ShopBy. ShopBy requires the deep search in order to function properly.

So I added a bool shallowSearch parameter to the interface and the AdvancedSearch methods. Since I modified the interface, I also had to update the signatures in the other search providers but the parameter is ignored.

The whole problem is literally fixed with 1 line of code.

Here's the updated method signature in the base search provider:
Code:
public virtual IList<Product> AdvancedSearch(string keyword, int categoryId, int manufacturerId, bool searchName, bool searchDescription, bool searchSKU, decimal lowPrice, decimal highPrice, bool onlyFeatured, IList<ShopByChoice> choices, string sortExpression = "", int maximumRows = 0, int startRowIndex = 0, bool shallowSearch = true)


And here's the code I added to the AdvancedSearch routine in the base search provider:
Code:

            // BUILD QUERY
            selectQuery.Append(selectColumns);
            selectQuery.Append(fromString);
            // BEGIN MOD: AbleMods.com
            // 9-2-2021
            // force search relationship into catalognodes to stay within the specified category i.e. don't include other category catalognode entries
            selectQuery.Append(" WHERE PC.CatalogNodeTypeId = " + (short)CatalogNodeType.Product);
            if (shallowSearch)
            {
                selectQuery.Append(" AND PC.CategoryId = :categoryId");
            }
            //selectQuery.Append(" WHERE PC.CatalogNodeTypeId = " + (short)CatalogNodeType.Product);
            // END MOD: AbleMods.com
            selectQuery.Append(" AND P.ProductId IN (");



Since I have a parameter, now I can control where I want to force shallow search versus when a deep search is still necessary.
Users browsing this topic
Similar Topics
Category sorting order not sorting properly (AbleCommerce 9 Bug Reports)
by ray22901031 8/7/2021 1:27:27 PM(UTC)
Category sorting issue (AbleCommerce 9 Bug Reports)
by judy at Web2Market 4/28/2021 11:06:24 AM(UTC)
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.