Developer forum

Forum » Development » How to sort products by custom product field

How to sort products by custom product field

Morten Snedker
Reply

Both helpdesk cases and Forum suggest that it is often wanted to sort products by a custom product field when products are rendered at frontend.

 

The example below will sort ascending by product field "MySorting". So, courtesy of Jeppe Agger:

 

using Dynamicweb;
using System.Collections.Generic;
using System.Linq;

namespace ProductTemplateExtender
{
    [Dynamicweb.Extensibility.Subscribe(Dynamicweb.Notifications.eCommerce.ProductList.BeforePaging)]
    public class EcomProductListBeforePagingObserver1 : Dynamicweb.Extensibility.NotificationSubscriber
    {
        public override void OnNotify(string notification, Dynamicweb.Extensibility.NotificationArgs args)
        {
            var myArgs = (Dynamicweb.Notifications.eCommerce.ProductList.BeforePagingArgs)args;

            var sortingDict = new Dictionary<object, List<Dynamicweb.eCommerce.Products.Product>>();    // new dictionary on which you'll perform the sorting
            foreach (var product in myArgs.Products)                                                    // you may want to specify type more closely than "object"
            {
                var fieldValue = product.GetProductFieldValue("MySorting");                             // retrieve the custom field you wish to use for sorting
                if (!sortingDict.ContainsKey(fieldValue))
                    sortingDict.Add(fieldValue, new List<Dynamicweb.eCommerce.Products.Product>());     // use List as same sorting value may appear more than once
                
                sortingDict[fieldValue].Add(product);
            }

            var col = new Dynamicweb.eCommerce.Products.ProductCollection();
            foreach (var item in sortingDict.OrderBy(x => x.Key))   // order the collection and add to new empty product collection
            {
                foreach (var product in item.Value)
                    col.Add(product);
            }

            myArgs.Products.Clear();                // clear original collection
            myArgs.Products.AddRange(col, true);    // pass the ordered collection to original collection
        }
    }
}

 

Regards /Snedker


Replies

 
Lars Larsen
Reply

Hi Snedker

Your example does not work if you have a paged productlist. In that case the notificationsubscriber is called for every page in the productlist and not just once for all pages in the list. So this code is not usefull in case you have a paged productlist.

Any ideas of how to solve sorting on customproductfields in paged productlists is much appreciated.

 

Regards

Lars

 
Morten Fink Eriksen
Reply

Would i be able to use this for dynamicsorting as well? By this i mean if i had some URL parameters like SortBy=Name and SortDir=Desc and i wanted to request them and use them for this type of sorting, would this be possible.

 

Out usecase is that the user should be able to choose sorting in the frontend.

 

Something like (pseudocode):

 

 

var sortBy = httpContext.request["SortBy"];
var sortDir = httpContext.request["SortDir"];


......

foreach (var product in myArgs.Products)                                                        
{                 

    var fieldValue = product.GetProductFieldValue(sortBy);
     if (!sortingDict.ContainsKey(fieldValue))  

           sortingDict.Add(fieldValue, new List<Dynamicweb.eCommerce.Products.Product>());    

    sortingDict[fieldValue].Add(product); 
}   

.....

foreach (var item in sortingDict.OrderBy(x => x.Key, SortDir)) 
{ 
    foreach (var product in item.Value) 
        col.Add(product); 
} 

 

 

 
Morten Snedker
Reply

Hi Morten,

 

Yes you can. In the case you can evaluate your query string and have it point to an EcomProducts column exposed by the Product object.

 

Regards /Snedker

 
Morten Fink Eriksen
Reply

Hi Morten

 

Still doesn't fix the problem with having paged results though, as the sorting gets done as stated earlier on a per page basis.

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Reply

This should only be a problem when using Optimized Product Retrieval. The reason is that only the products for the given page is fetched from the database, and therefore it's not possible to sort the entire collection.

 

When Optimized Product Retrieval is turned off, this notification ssubscriber does indeed sort the entire collection. The args for the BeforePaging notification contains all products so sorting the collection at this time is possible.

 

Basically, the eCom frontend flow is like this when OPR is off (simplified):

1) Fetch all products matching the settings on the Paragraph

2) Sort based on the Paragraph settings

3) Fire BeforeSort notification (as this is not related to sorting settings on the Paragraph)

4) Sort based on QueryString parameters (if available)

5) Reorder according to OMC profile

6) Fire BeforePaging notification <-- This is where we come in

7) Do the paging

8) All the rest including render

 

Hope this helps to clarify the situation :)

 

- Jeppe

 
Anders Ebdrup
Reply

Hi Jeppe,

 

Do you have a notification subscriber, which can be used to sort the products when having Optimized Product Retrieval turned on?

 

Best regards, Anders

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Reply

No. You cannot sort the optimized collection as it only contains the actual products being displayed on that given page and not all the rest. This is the base feature of the optimized product retrieval, that only the needed products are fetched from the database. So, if you configure the paragraph to show 8 products per page, then only 8 products are queried. This is why this sorting will not work for OPR.

 

OPR is still new and we are continuosly improving it. Currently, I'm not sure how we would go about implementing this type of functionality here. But I must admit, I haven't given it too much thought. As it stands right now, OPR respects the sorting setting on the Paragraph but this is only because the values are available on the EcomProducts table in the database and we therefore can use the query to sort.

 
Anders Ebdrup
Reply

Hi Jeppe,

 

I have now disabled the OPR as I need to sort the products with this result:

System.Data.SqlClient.SqlException (0x80131904): Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dynamicweb.Database.CreateDataReader(String sql, String database, IDbConnection connection, Int32 commandTimeout)
   at Dynamicweb.eCommerce.Prices.Price.LoadPricesOnProducts(ProductCollection Products)
   at Dynamicweb.eCommerce.Frontend.Frontend.GetProductsFromIndex(IEnumerable`1 filters)
   at Dynamicweb.eCommerce.Frontend.Frontend.EcomRender()
   at Dynamicweb.eCommerce.Frontend.Frontend.GetContent()
   at Dynamicweb.Frontend.Content.getModule(DataRow& ParagraphRow)

Something seems to be wrong - please help!

 

Best regards, Anders

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Reply

According to Microsoft, this issue is caused because the limit of identifiers and constants in a given query was reached. That limit is 65.535 (http://support.microsoft.com/kb/913050). I don't know specifically why you're experiencing this issue as I've never seen it before in a Dynamicweb solution, though I'm pretty sure it's not related to OPR.

 

Do you have a site where I can see this for myself? Or perhaps I can get a copy of the site so I can test it locally?

 
Anders Ebdrup
Reply

Hi Jeppe,

 

Then it sounds reasonably as the solution has 47.000+ products in two languages. Can you please send me your email-address so I can share it on my SkyDrive?

 

Best regards, Anders

 
Jeppe Eriksson Agger Dynamicweb Employee
Jeppe Eriksson Agger
Reply

You can contact me at jea@dynamicweb.dk

 

You must be logged in to post in the forum