Search orders and sort by Meta Fields

Syed Quadri

Vote:

I am searching for PurchaseOrders using the ordercontext.Search method.

var orders = orderContext.Search<PurchaseOrder>(orderSearch, out count)

Here I can specify a where clause on the MetaField using the property SqlMetaWhereClause on the OrderSearchParameters. But how can i perform sort on the meta field.

Coming from version 7.5, we got away by writing the order by clause in the SqlMetaWhereClause itself. But after upgrading to the 13.26, its breaking with the error - The ORDER BY clause is invalid in views, inline functions, derived tables ..... as the underlying meta tables are converted to views.

Now is there any way I can do an order by on meta fields?

Thanks.

#230693

Nov 11, 2020 15:36

Quan Mai

Vote:

I might be terribly outdated but I don't think the order tables have been converted to views. What were "converted" was catalog tables in Commerce 9

#230702

Nov 11, 2020 21:55

Naveed Ul-Haq

Vote:

Get Sorted list of PurchaseOrder for logged-in customers (mostly used for order history)

var purchaseOrders = OrderContext.Current.LoadByCustomerId<PurchaseOrder>(PrincipalInfo.CurrentPrincipal.GetContactId())
                                             .OrderByDescending(x => x.Created)
                                             .ToList();

Get the list of orders using Order search (normally used if you need to create a report or export orders to external integration point)

public IEnumerable<PurchaseOrder> GetEpiOrders(int maxOrders)
        {
            var parameters = new OrderSearchParameters
            {
                SqlWhereClause = "Your-Criteria-to get-qualified-orders"
            };
            var classes = new StringCollection { OrderContext.Current.PurchaseOrderMetaClass.Name };

            var searchOptions = new OrderSearchOptions { Classes = classes, RecordsToRetrieve = maxOrders };
            var orders = OrderContext.Current.Search<PurchaseOrder>(parameters, searchOptions);

            return orders;
        }

I hope that helps

#248166

Feb 04, 2021 17:21

Sanjay Kumar

Vote:

In the Episerver Commerce 13.x the Order By clause only works for 'OrderGroupId', we can not pass any meta field name to sort the records.

e.g. orderSearchParameters.OrderByClause = "OrderGroupId DESC";

This would probably be a feature request for a future release.

#248171

Feb 04, 2021 19:01