Searching for orders

The Episerver Commerce API searches for and retrieves orders. This topic describes how to work with order search in Episerver Commerce, including:

The capabilities and limitations of Episerver Commerce order search
The API: classes and properties used
Code examples of different scenarios
Description of the search stored procedure

Searching for orders is a common part of e-commerce solutions, such as when you integrate with ERPs and custom administrative interfaces where orders are exported, filtered, or edited. You often need to load orders based on submission date, status, or particular properties or meta-field properties of the order or cart, including properties of its child objects (for example, shipments or line items).

Classes in this topic are available in the Mediachase.Commerce.Orders namespace.

Search pattern

The following pattern is the simplest way to execute order search in Episerver Commerce using the API.

Begin the 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 continue with:

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 .).

How to use order search

The order search returns arrays of OrderGroup objects: ShoppingCart, PaymentPlan, PurchaseOrder. For information about the order object and table hierarchy, see Order System/Order Object Model and Database Diagram.

The order search is not intended for returning shipments, line items, payment objects or any other objects/data, except in the context of the whole order. The Order Search API does not do an order search for those types of objects alone. The API lets you 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 has two parts:

Get a list of OrderGroupIds as search results using the stored procedure ecf_OrderSearch. Insert those values into a database table OrderSearchResults (with a GUID that separates it from other search results). This is the search phase, based on criteria passed to the search.
The OrderGroupIds are loaded from OrderSearchResults, and the corresponding array of ShoppingCart/PurchaseOrder/PaymentPlan is loaded and returned.

Note: You can sort based on OrderGroup table fields only.

Primary classes and properties

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

Properties

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

RecordsToRetrieve
StartingRecord
Integer. Use these fields to page search results.

Classes

StringCollection
This lets you specify the order metadata object on which you want to create a subquery. For example, to find all shipments where a shipment meta-field value is a particular value, specify ShipmentEx for the classes property. This is a string collection. In most cases, only one class needs to be specified.

You can specify the following classes with the corresponding database table that is 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: You must specify at least one class, even if you don't specify a SQLMetaWhereClause in the parameters object. If you do not specify SQLMetaWhereClause but do specify an OrderGroup (PurchaseOrder, ShoppingCart, PaymentPlan), it filters 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 lets you specify where clauses necessary to narrow the order search.

Properties

OrderByClause
This clause can use only OrderGroup table fields. If this field is not set, OrderGroupId is used.

SqlMetaWhereClause
This field matches up with the OrderSearchOptions.Classes field to form a subquery based on an OrderGroup meta-class. 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
A SQL WHERE clause that lets you specify additional order search criteria to form a subquery in the search stored procedure (see the stored procedure breakdown later in this topic).

Using the META keyword here refers to a different alias than using it in the SqlMetaWhereClause, where it is 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. The OrderGroupId.
Rank. Part of the meta WHERE clause subquery. (You probably do not want to use META here.)

Only the the OrderGroup table is referenced directly in the SQL WHERE clause. So, you could have a clause like: OrderGroupId IN (SELECT OrdergroupId FROM Shipment WHERE NOT ShipmentTrackingNumber IS NULL).

Note: You cannot add an ORDER BY clause here, because it is part of a subquery.

AdvancedFreeTextSearchPhrase and FreeTextSearchPhrase
These fields do not work. Leave them blank.

The following 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(). Retrieves PurchaseOrders where the OrderGroup.Status is either InProgress or PartiallyShipped.
FindCarts(). Finds shopping carts based on parameters and options specified with the two other classes.
FindPaymentPlans(). Finds PaymentPlans based on parameters and options specified with the two other classes.
FindPurchaseOrders(). Finds PurchaseOrders based on parameters and options specified with the two other classes.
FindPurchaseOrdersByStatus(). Retrieves orders with particular order statuses.

Code examples

The code is common and first defined for all of the following code examples.

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

The following examples have code that follows the code above.

Example 1

Retrieves purchase orders with a tracking number that is like a particular pattern, and with a tracking number (a meta-field) 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);

Example 2

Retrieves 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);

Note: The SqlWhereClause is used to ensure that only purchase orders are returned. If you do not 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.

This is because the metadata object load is getting confused by the different meta-fields in a purchase order vs a cart. In cases where you are querying orders using the API, and do not specify 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.

Example 3

Retrieves shopping carts where line items 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);

Example 4

Retrieves purchase orders 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);

Example 5

This is 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.

Note: You do not have an SqlMetaWhereClause but do specify a PurchaseOrder class.

You can add any subquery, 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);

Example 6

Joins several order metadata tables 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).

Note: The following query appears to have the potential to return duplicate ordergroupids as an order can have multiple shipments or line items. 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);

Note: Always test queries in SQL Management Studio first, and 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.

This is the final query that is built based on the settings you specify. This is assuming that the AdvancedFreeTextSearchPhrase and FreeTextSearchPhrase properties are left blank (see above). 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 ) 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;

Note the following:

The query for metadata 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.
The Application ID is managed internally by Episerver Commerce.