Multi-Category Product Sorting in Category Details Page

For general questions and discussions specific to the AbleCommerce GOLD ASP.Net shopping cart software.
Post Reply
Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Mon Apr 23, 2018 4:22 am

Problem: When using the CategoryGrid2 page, and have products that are in multiple categories, the Order By view uses the Product.OrderBy field, which due to products being in multiple categories isn't necessarily correct (as there are now (2) Order By: 5.00 as well as (2) Order By: 6.00, and then it jumps to 10.00. However, the CatalogNodes table does hold the correct Order By value.

How am I able to link up the CatalogNode table to the Product table to use the CatalogNode OrderBy (I know the CatalogNodeID = ProductID) in the CategoryGridPage2.ascx.cs file since it's currently using:

Code: Select all

IList<Product> visibleNodes = ProductDataSource.LoadForCategory(true, this.CategoryId, false, true, SortResults.SelectedValue, _pageSize, (_HiddenPageIndex * _pageSize));
and this returns the CommerceBuilder.Products.Product reference, rather than a simple C# reference (at least that I'm used to) to do a LINQ join with.

Also keep in mind that I'm brand spanking new to this platform (and project as well), so some hand holding would be greatly appreciated.

Version details:
AbleCommerce for ASP.NET
VERSION: 7.0.92.9266
Release Label: GoldR12SR1
(edited: Changed initial description of Category Details to CategoryGrid2)
Last edited by Christopher Kiraly on Tue Apr 24, 2018 7:14 am, edited 1 time in total.

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Mon Apr 23, 2018 8:15 am

Not sure if this is the most elegant or "correct" way :), but perhaps something like:

Code: Select all

IList<int> catNodeOrder = NHibernateHelper.CreateSQLQuery("select CatalogNodeId from ac_CatalogNodes where CategoryId = " + this.CategoryId + " order by OrderBy").List<int>();
visibleNodes = visibleNodes.OrderBy(vn => catNodeOrder.IndexOf(vn.Id)).ToList();
Disclaimer: No errors are flagged by the editor, but I did not try compiling or testing performance. YMMV :).

EDIT: Obviously, you would need to wrap this in an if statement so it only executes if the user has selected the appropriate item from the SortResults drop-down.

EDIT 2: Fixed suggested query; original incorrect suggestion was "select OrderBy ..."
Last edited by jguengerich on Wed Apr 25, 2018 7:55 am, edited 1 time in total.
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Tue Apr 24, 2018 7:19 am

Jay -

Thanks for the response... however, when I try your idea, my page returns

Code: Select all

We are sorry, but the page you are trying to access has experienced an error.
What I did was based on what you said. My new code looks like:

Code: Select all

IList<Product> visibleNodes = ProductDataSource.LoadForCategory(true, this.CategoryId, false, true, SortResults.SelectedValue, _pageSize, (_HiddenPageIndex * _pageSize));
IList<int> catalogNodeOrder = NHibernateHelper
    .CreateSQLQuery("select OrderBy from ac_CatalogNodes where CategoryID = " + this.CategoryId + " order by OrderBy")
    .List<int>();
visibleNodes = visibleNodes
    .OrderBy(vn => catalogNodeOrder.IndexOf(vn.Id))
    .ToList();
I'm going to keep plugging away and see what I can come up with...

I was originally thinking about just doing my own query - but I'm worried about not providing all the 'fields' that are needed to make this work.

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Wed Apr 25, 2018 12:35 am

Is there a more detailed error message in the error log? (Help > Error Log in Admin, or [site root path]\App_Data\Logs\app.log on the server).
Jay

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Wed Apr 25, 2018 2:40 am

Regarding writing your own query, it may work as long as you use a method that returns an IList<Product>; you can assign it to visibleNodes just like the standard code. There several ways to do it with NHibernate, including Native SQL, Criteria, HQL, QueryOver, and Linq.

I say "may work" because the LoadForCategory method builds a fairly complicated query. If you purchased the AbleCommerce source, you could customize the repository code (add another overload of LoadForCategory, add a different method, etc.) in ProductRepository.cs. If you don't have the source to look at, reproducing that query in CategoryGrid2 will be challenging; you can see from the parameters that it has to account for group restrictions, public visiblity, paging, etc. It also does some eager loading of related data.

Maybe someone from AbleCommerce will post some suggestions?
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Wed Apr 25, 2018 5:20 am

jguengerich wrote:Is there a more detailed error message in the error log? (Help > Error Log in Admin, or [site root path]\App_Data\Logs\app.log on the server).
Here is what the admin Error Log supplied:

Code: Select all

Exception: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Inner Exception: The value "0" is not of type "System.Int32" and cannot be used in this generic collection. Parameter name: value Inner Exception Stack Trace: at System.ThrowHelper.ThrowWrongValueTypeArgumentException(Object value, Type targetType) at System.Collections.Generic.List`1.System.Collections.IList.Add(Object item) at NHibernate.Util.ArrayHelper.AddAll(IList to, IList from) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List[T]() at AbleCommerce.ConLib.CategoryGridPage2.Page_Load(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Wed Apr 25, 2018 5:43 am

Thanks, that 'value of "0" is not of type "System.Int32"' led me to notice the mistake in the query I suggested. It should be "select CatalogNodeId ..." instead of "Select OrderBy ...".
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Wed May 16, 2018 4:33 am

First - Thank you!!! That solved my original problem!!!

Now I've come across something that doesn't make any sense at all...

For a different Product category, this new custom view is not working... When I query the DB (outside of ablecommerce), and view the CatalogNode table (and link it to product) and order the Products by Order By (CatalogNode), I get the Order By from 0 - 13 (for 14 Products), but when I go to the page, the Default order is 0-9, 12-13, 10-11.

And I have no idea as to why this is happening, nor how to even diagnose why it is happening...

Any help on this one would be greatly appreciated...

I played around a little with the Product Order By (which, using your query, shouldn't affect the display order), if I made that Order By a lower number than the items coming before it, it shows where it should (almost like after the CatalogNode OrderBy 9, it switches to the Product OrderBy)

This whole bit doesn't make sense... and I could use some expertise on it!

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Wed May 16, 2018 5:23 am

Make sure the query in your code is:

Code: Select all

select CatalogNodeId from ac_CatalogNodes where CategoryId = " + this.CategoryId + " order by OrderBy
The select clause should select the CatalogNodeId field, and the order by clause should sort on the OrderBy field.
Also, if multiple items in the category have the same value in the ac_CatalogNodes.OrderBy field, this query makes no guarantees that the order of those CatalogNodeIds in the query results will be consistent across queries. I guess you could modify the order by clause of the query to make it consistent in those cases:

Code: Select all

select CatalogNodeId from ac_CatalogNodes where CategoryId = " + this.CategoryId + " order by OrderBy, CatalogNodeId
You'd always get the same order, but since you can't control the CatalogNodeId, it may not be a "useful" order. Probably best to make sure each node's OrderBy is unique within the category. Which brings up the point that the OrderBy field is a smallint, so if you get more than 32,767 items in a category, some of your OrderBy fields will have to be duplicates.
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Thu May 17, 2018 1:43 am

Here is the code right now:

Code: Select all

IList<Product> visibleNodes = ProductDataSource.LoadForCategory(true, this.CategoryId, false, true, SortResults.SelectedValue, _pageSize, (_HiddenPageIndex * _pageSize));
IList<int> catalogNodeOrder = NHibernateHelper
    .CreateSQLQuery("select CatalogNodeId from ac_CatalogNodes where CategoryID = " + this.CategoryId + " order by OrderBy")
    .List<int>();
visibleNodes = visibleNodes.OrderBy(vn => catalogNodeOrder.IndexOf(vn.Id)).ToList();
and then everything else is the standard CategoryGridPage2 page. Adding the CatalogNodeId to the OrderBy did not change my order in any way. (Which I figured it wouldn't).

I have visually verified in the DB querying the ac_CatalogNodes table for the CategoryID that the OrderBy entries are unique.

The funny thing is, after playing around in the Category section of the BackOffice arranging them to verify they are in the right order, when I go to Preview, it shows them in the wrong order.

The odd thing is that this isn't even following the pattern of the original issue. (Where it was using the OrderBy from Product).

I am wondering if I can add an ad hoc update query to run at the top of the page updating the order by in the Products table based on the value in CatalogNodes. Is this possible, and is it advisable. The Product list for this vendor is relatively small (less than 100 items) so it shouldn't have too much of an overhead cost... I figured either adding a stored procedure to run or just putting the query in the code behind, would solve this problem for products in multiple category sorting. Just not sure how to implement this in the code behind!

Otherwise, I'm open to other ideas to try.

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Thu May 17, 2018 1:59 am

Hmm, not sure what's going on then. If you run this query directly (outside of AbleCommerce):

Code: Select all

select CatalogNodeId from ac_CatalogNodes where CategoryID = 47 order by OrderBy --or whatever the category id is
are the catalog node ID's in the order you expect? Remember, you will be getting catalog node IDs (product IDs) back, not the OrderBy numbers.
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Thu May 17, 2018 4:30 am

Yes - I ran the following modified query in Mgmg Studio:

Code: Select all

select CatalogNodeId, p.Name 
from ac_CatalogNodes cn
left outer join ac_Products p on p.ProductId = cn.CatalogNodeId
where CategoryID = 10 order by cn.OrderBy
where 10 is the Category in question -

The results are the products in the expected order

The actual page order that they appear for the 14 Items are almost correct - its that the last 2 items appear BEFORE the 2 items before it. Almost like starting with item 10, it used the Product OrderBy, and not the CatalogNode OrderBy (at that point, because the first 10 items are specifically in CatalogNode OrderBy order, and not Product OrderBy.

So it's all wonky.

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Thu May 17, 2018 4:48 am

Just as a troubleshooting step though, if you run the exact query you have on the web page but from Mgmt Studio, without the join to products, do the node IDs come back in the correct order?
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Thu May 17, 2018 6:47 am

Yes -

When I run both queries in Mgmt Studio, I get the same order on the Products:

Product/CatalogNodeId : 57 | 62 | 135 | 136 | 69 | 70 | 71 | 72 | 73 | 74 | 76 | 75 | 54 | 59

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Thu May 17, 2018 7:07 am

Oh, maybe it has to do with the paging? LoadForCategory determines which records to return based on the page size and page index, but it is ordering things using the Product OrderBy field, then counting where to start and how many to return in that order. I should have thought of that earlier, sorry.
I guess the next step is to analyze the following using the debugger or creating error log entries:

Code: Select all

IList<Product> visibleNodes = ProductDataSource.LoadForCategory(true, this.CategoryId, false, true, SortResults.SelectedValue, _pageSize, (_HiddenPageIndex * _pageSize));
// A. what is the order of the IDs in visibleNodes here?
IList<int> catalogNodeOrder = NHibernateHelper
    .CreateSQLQuery("select CatalogNodeId from ac_CatalogNodes where CategoryID = " + this.CategoryId + " order by OrderBy")
    .List<int>();
// B. what is the order of the IDs in catalogNodeOrder here?
visibleNodes = visibleNodes.OrderBy(vn => catalogNodeOrder.IndexOf(vn.Id)).ToList();
// C. what is the order of the IDs in visibleNodes here?
D. what is the order of the IDs on the rendered page (by looking up the IDs of the product names)

If the answer is not as expected at B, not sure what the problem is, that is just a simple query with an order clause, so it should give the same as running it in Mgmt Studio.
If the answer is not as expected at C, maybe the .OrderBy(...IndexOf...) trick doesn't work. Seems like it should though.
if the answer is not as expected at D, then maybe it has to do with the paging, or some other code is changing the order after C but before the results are rendered.
Jay

jguengerich
Commodore (COMO)
Commodore (COMO)
Posts: 436
Joined: Tue May 07, 2013 1:59 pm

Re: Multi-Category Product Sorting in Category Details Page

Post by jguengerich » Thu May 17, 2018 7:27 am

If it is the paging that causes the problem, I guess you could call LoadForCategory without the last 2 parameters (it is overloaded), then do your own sort, then use the page size and index to remove unwanted items from visibleNodes. However, if you do that, the more items in the category, the longer it will take to load the page because you're loading everything in the category every time.

I don't think your idea of updating the product's OrderBy every time would work - if a product is in multiple categories, and one customer looks at one category while another customer looks at the other, the product's OrderBy would keep switching. There'd be no guarantee that the Product OrderBy would be whatever you just set it to by the time you query it again. If you manually guarantee that those products are only in that one category, and only do your custom code when showing that category, I guess it might work for a relatively small quantity of items.

Probably the best way to do it is to modify or overload LoadForCategory, but you need to purchase the source code to do that.
Jay

Christopher Kiraly
Ensign (ENS)
Ensign (ENS)
Posts: 18
Joined: Fri Apr 20, 2018 7:32 am

Re: Multi-Category Product Sorting in Category Details Page

Post by Christopher Kiraly » Fri May 18, 2018 1:36 am

jguengerich wrote:If it is the paging that causes the problem
BINGO!!!

Of course the problem is that the reordering is occurring over items 11-14, and 13 & 14 aren't coming in as it's only pulling the top twelve!!!

As the client actually prefers not having the pagination in it (and we are only talking about a max of 30+ items), I'm just going to yank out the pagination piece!

Basically what I did here was leave the code behind along (as it already is with the CatalogNode attachment) but change the Page Size drop down to default to 1000 items (Saying "All Items") and then disabling it so it can't be changed. All is well after that!

Once again, thank you for your time on this... I'm very new to AbleCommerce and not being able to interact was sort of frustrating!

[edited for spelling]

Post Reply