EPiServer Commerce Order Search Made Easy

Searching for orders is a common part of ECommerce solutions, usually for integration with ERPs and custom administrative interfaces where orders are to be exported, filtered, or edited. Orders often need to be loaded based on date of submission, their status, or on particular properties or meta field properties in the order or cart, including properties of its child objects (for example shipments or lineitems). EPiServer Commerce (R1-R3) offers an API to search for and retrieve orders. This blog will cover :

     * The capabilities and limitations of EPiServer Commerce order search
  * The API - classes and properties you need to understand
  * Code examples of different scenarios
  * An in-depth look at the search stored procedure that does most of the search work

The Quick And Easy Summary

The simplest way to do order search in EPiServer Commerce using the API is to follow this pattern:

Always begin your search code with:

OrderSearchOptions searchOptions = new OrderSearchOptions();
searchOptions.CacheResults = false; //or true if the results may be recalled shortly
searchOptions.StartingRecord = 0; //or whatever you want to specify for paging purposes
searchOptions.RecordsToRetrieve = 10000;  //or whatever you want to specify for paging purposes
searchOptions.Namespace = "Mediachase.Commerce.Orders";

Then

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder"); //this returns only purchase orders, for shopping carts, specify ShoppingCart
parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions); //If you are searching for shopping carts, you would call the FindCarts method and return an array of Cart objects

The sqlQuery above is of the form : “OrderGroupId IN (SELECT OrderGroupId FROM ….<your particular subquery filtering for the correct orders>)”. See the code examples below.

Capabilities/Limitations

The order search is designed to return arrays of OrderGroup objects - ShoppingCart, PaymentPlan, PurchaseOrder. If you're not familiar with the order object and table hierarchy, go here. Look under Order System/Order Object Model and Database Diagram.
Its not for returning shipments, lineitems, payment objects or any other objects/data in an OrderGroup, except in the context of the whole order. If you need to do an order search for those types of objects alone, the Order Search API won't help you. The API allows you to search for carts/orders/payment plans based on properties, including meta fields associated with any of the objects in an OrderGroup and any outside database tables you may want to use for filtering/joins.

The search pattern implemented in the API is two parts : First get a list of OrderGroupIds as search results using the stored procedure ecf_OrderSearch and insert those values into a database table – OrderSearchResults (with a GUID that separates it from other search results). This is the search phase, which is based on the criteria you pass in to the search. Next, the OrderGroupIds are loaded from OrderSearchResults and the corresponding array of ShoppingCart/PurchaseOrder/PaymentPlan is loaded and returned.

One other noteworthy limitation is that sorting can only be done based on OrderGroup table fields.

Primary Classes / Properties

Mediachase.Commerce.Orders.Search.OrderSearchOptions
This class allows you to specify the number of records to be returned, whether the results are to be cached, the type of OrderGroup data to be returned (e.g. ShoppingCart/PurchaseOrder/PaymentPlan), starting record number (for paging).

Properties
CacheResults
Boolean. Set to true if the results may need to be re-retrieved soon.

RecordsToRetrieve
StartingRecord
Integer. You can use these two fields to do paging on the search results

Classes

StringCollection. This allows you to specify which order metadata object you want to create a subquery on. For example, if you wanted to find all shipments where a shipment meta field value is a particular value, you could specify "ShipmentEx" for the classes property. This is a string collection but I'd be hard pressed to find an example where more than one class needs to be specified (of course, someone will now come forward to point out a valuable use for multiple-class queries - to which I say "bring it on"). Here are all of the classes you could specify (with the corresponding database table that will be queried).

CashCardPayment (OrderFormPayment_CashCard)
CreditCardPayment (OrderFormPayment_CreditCard)
ExchangePayment (OrderFormPayment_Exchange)
GiftCardPayment (OrderFormPayment_GiftCard)
InvoicePayment (OrderFormPayment_Invoice)
LineItemEx (LineItemEx)
OrderFormEx (OrderFormEx)
OrderGroupAddressEx (OrderGroupAddressEx)
OtherPayment (OrderFormPayment_Other)
PaymentPlan (OrderGroup_PaymentPlan)
PurchaseOrder (OrderGroup_PurchaseOrder)
ShipmentEx (ShipmentEx)
ShoppingCart (OrderGroup_ShoppingCart)

Note that you do need to specify at least one class here, even if you don’t specify a SQLMetaWhereClause in the parameters object (below). If you don’t specify SQLMetaWhereClause but do specify an OrderGroup (“PurchaseOrder”, “ShoppingCart”, “PaymentPlan”), it will filter the results for that ordergroup type only.

Namespace
For order searches, this should always be Mediachase.Commerce.Orders

Mediachase.Commerce.Orders.Search.OrderSearchParameters
This class allows you to specify the where clauses necessary to narrow your order search.

Properties
OrderByClause
This clause can only use ordergroup table fields. If this field isn’t set, OrderGroupId is used.

SqlMetaWhereClause
This field matches up with the OrderSearchOptions.Classes field to form a subquery based on an OrderGroup metaclass. This clause looks like this:
"META.CardType = 'Visa'" (where the option classes collection contains "CreditCardPayment"). That returns all orders/carts/payment plans with visa payments. META is the SQL alias for the table associated with the class you specify (see options Classes property above).

SqlWhereClause
This is a SQL WHERE clause that you can use to specify additional criteria in your order search. This where clause is used to form a subquery in the search stored procedure (see the stored procedure breakdown below). Note that using the META keyword here will be referring to a different alias than using it in the SqlMetaWhereClause. In the SqlMetaWhereClause, it's an alias for the table associated with the class specified in the options object. In the SQLWhereClause, it refers to a table in memory that has two columns - Key (which is the OrderGroupId) and Rank, which is part of the meta where clause subquery - you probably won't want to use META here. The only table referenced directly in the SQL where clause is the OrderGroup table. So you could have a clause like : “OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL)”. Note that you can’t add an “ORDER BY” clause here as its part of a subquery (see stored procedure description below).

AdvancedFreeTextSearchPhrase
FreeTextSearchPhrase
This doesn’t work. These fields should be left blank.

These fields are NOT USED for order search
JoinSourceTable
JoinSourceTableKey
JoinTargetQuery
JoinTargetTableKey
JoinType

Mediachase.Commerce.Orders.OrderContext
This class is a singleton with several methods to find orders:

FindActiveOrders() To retrieve all PurchaseOrders where the OrderGroup.Status is either InProgress or PartiallyShipped.
FindCarts() allows you to find shopping carts based on parameters and options specified with the two other classes.
FindPaymentPlans() Same as FindCarts except with PaymentPlans
FindPurchaseOrders() Same as FindCarts except with PurchaseOrders
FindPurchaseOrdersByStatus() allows you to retrieve orders with particular order statuses

Code Examples

For all of the code examples, the following code is common and first defined:

OrderSearchOptions searchOptions = new OrderSearchOptions();
searchOptions.CacheResults = false;
searchOptions.StartingRecord = 0;
searchOptions.RecordsToRetrieve = 10000;
searchOptions.Namespace = "Mediachase.Commerce.Orders";

The following examples are the code that follows the above code.

1. Here’s an example of retrieving all purchase orders with a tracking number that is like a particular pattern and where a tracking number (a meta field) has been assigned to at least one of the shipments in the purchase order:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlMetaWhereClause = "META.TrackingNumber LIKE '%PO%'";
parameters.SqlWhereClause = "OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL)";

PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

2. Here’s an example of retrieving all purchase orders that contain line items that have an RMANumber (a meta field) associated with them:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("LineItemEx");
parameters.SqlMetaWhereClause = "NOT META.RMANumber IS NULL";
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";

PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

NOTICE: the SqlWhereClause is used to ensure that only purchase orders are returned. If you don’t do this, you may get carts (or payment plans, if they exist in your system) also in the dataset and you will get errors like :

Index 0 is either negative or above rows count.

As the metadata object load is getting confused by the different meta fields in a purchase order vs a cart. In any cases where you’re querying orders using the API and you’re not specifying which OrderGroup object as the meta class you are seeking, you need to add this where filter in the SQLWhereClause. Of course, you can make a compound where clause including this filter…

3. Here’s an example of retrieving all shopping carts where lineitems have an expiration date (a meta field) greater than a particular value:

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("LineItemEx");
parameters.SqlMetaWhereClause = "META.ExpirationDate > '1/1/2012'";
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_ShoppingCart)";
Cart[] cartCollection = OrderContext.Current.FindCarts(parameters, searchOptions);

4. Here’s an example where all purchase orders are retrieved that have shipments with a particular a shipping status (a meta field) :

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("ShipmentEx");
parameters.SqlMetaWhereClause = "NOT META.PrevStatus IS NULL";
parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";

PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

5.Here’s one with a more complex SQL where clause to return all orders that contain SKUs whose name contains a substring by using a join with the CatalogEntry table. Notice that I don’t have a SqlMetaWhereClause but do specify a PurchaseOrder class.

You can add any subquery here, allowing the most flexibility.

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("OrderGroupId IN (SELECT OrderGroupId From LineItem li ");
sqlQuery.Append("INNER JOIN CatalogEntry en ");
sqlQuery.Append("ON li.CatalogEntryId = en.Code ");
sqlQuery.Append("WHERE en.Name LIKE '%Wine%')");

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

6. Finally, here’s an example where several order meta data tables are joined in the SQL where clause. This is a better, easier way to do more complex order searches with multiple order meta classes (rather than using the options Classes collection for filtering order meta classes). Notice that the query below appears to have the potential to return duplicate ordergroupids as an order can have multiple shipments or lineitems. However, the order search stored procedure ensures that only distinct order ids are returned.

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("OrderGroupId IN (SELECT li.OrderGroupId From LineItem li ");
sqlQuery.Append("INNER JOIN LineItemEx ex ");
sqlQuery.Append("ON li.LineItemId = ex.ObjectId ");
sqlQuery.Append("INNER JOIN Shipment sh ");
sqlQuery.Append("ON li.OrderGroupId = sh.OrderGroupId ");
sqlQuery.Append("INNER JOIN ShipmentEx shex ");
sqlQuery.Append("ON sh.ShipmentId = shex.ObjectId ");
sqlQuery.Append("WHERE ex.ExpirationDate > '1/1/2011'");
sqlQuery.Append("AND NOT shex.PrevStatus IS NULL)");

OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);

Some general tips when you’re writing your queries and adding them to your code:

* Always test them in SQL Management Studio first

* Remember the closing “)” for your SqlWhereClause property value.

Stored procedure breakdown
The first part of the order search is to perform a dynamic search for orders based on the OrderSearchParameters and OrderSearchOption properties set in code.

Here's the final query that's built based on the settings you specify. This is assuming that the AdvancedFreeTextSearchPhrase and FreeTextSearchPhrase properties are left blank.
The stored procedure which uses these properties to create a list of order ids is ecf_OrderSearch.

declare @Page_temp table (TotalRecords int, OrderGroupId int);
with OrderedResults as
    (SELECT count([OrderGroup].OrderGroupId)
      OVER() TotalRecords, [OrderGroup].OrderGroupId, Rank, ROW_NUMBER()
      OVER(ORDER BY <OrderBy property>) RowNumber 
      FROM [OrderGroup] OrderGroup
      INNER JOIN
        (select distinct U.[KEY], U.Rank from
        (select 100 as 'Rank', META.ObjectId as 'Key', * from <database table associated with the order metaclass in the options Classes property> META
         WHERE <SQLMetaWhereClause>) U) META
      ON OrderGroup.[OrderGroupId] = META.[KEY] 
      WHERE ApplicationId = '[Application ID]' AND
      (<SQLWhereClause>))
     
      INSERT INTO @Page_temp (TotalRecords, OrderGroupId)
           SELECT top(<RecordsToRetrieve>) TotalRecords, OrderGroupId FROM OrderedResults
           WHERE RowNumber > <StartingRecord>;;
        
      select @RecordCount = TotalRecords from @Page_temp;
        
      INSERT INTO OrderSearchResults (SearchSetId, OrderGroupId)
           SELECT [SearchSetId], OrderGroupId from @Page_temp;

I’ve added formatting to make it a bit easier to read. Some things to note :

* The query for meta data is built using the class you specify and the SQLMetaWhereClause you specify (already mentioned above). The META where clause *inside* the SQLMetaWhereClause is different from the META alias available to the SQLWhereClause.

* If no SQLWhereClause is specified, the query executes without the AND [SQLWhereClause] portion. The same goes for SQLMetaWhereClause.

* Don’t worry about Application ID. This is managed internally by EPiServer Commerce.