Search for orders using OrderContext

20 min

This topic describes how to work with the order search API, including:

The capabilities and limitations of Optimizely Commerce Connect order search

API classes and properties

Code examples of different scenarios

A description of the search stored procedure

Order search is a common part of ecommerce solutions, including ERP integrations 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 run an order search in Commerce Connect using the API.

Begin the search code with:

OrderSearchOptions searchOptions = new OrderSearchOptions();
searchOptions.StartingRecord = 0; // Adjust for paging.
searchOptions.RecordsToRetrieve = 10000; // Adjust for paging.
searchOptions.Namespace = "Mediachase.Commerce.Orders";

Then continue with:

OrderSearchParameters parameters = new OrderSearchParameters();

// The following is for returns-only purchase orders. For shopping carts, specify ShoppingCart.
parameters.SqlMetaWhereClause = "";
searchOptions.Classes.Add("PurchaseOrder");

parameters.SqlWhereClause = sqlQuery.ToString();

// If you are searching for shopping carts (non-serializable cart system), call Search<Cart> and return an array of Cart objects.
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.Search<PurchaseOrder>(parameters, searchOptions);

The SQL query above resolves to OrderGroupId IN (SELECT OrderGroupId FROM <table>), where <table> is determined by the OrderSearchOptions.Classes collection.

Use order search

The order search returns arrays of OrderGroup objects: ShoppingCart, PaymentPlan, and PurchaseOrder.

The order search is not intended to return shipments, line items, payment objects, or any other child objects or data of the order. The order search API does not search for those object types directly. The API lets you search for carts, orders, and payment plans by their properties — including metafields associated with any object in an OrderGroup — and by joining external database tables for filtering.

The search pattern implemented in the API has two parts:

Search phase – Use the ecf_OrderSearch stored procedure to get a list of OrderGroupId values matching the criteria. Insert those values into the OrderSearchResults table with a GUID that distinguishes them from other search results.

Load phase – Load the OrderGroupId values from OrderSearchResults and return the corresponding array of ShoppingCart, PurchaseOrder, or PaymentPlan objects.

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

Primary classes

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, and the starting record number (for paging).

Properties

CacheResults – Inherited from SearchOptions, but not used by order search.

RecordsToRetrieve

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

Classes

StringCollection – Specifies the order metadata object on which to create a subquery. For example, to find all shipments where a shipment metafield matches a particular value, specify ShipmentEx for the Classes property. This is a string collection. In most cases, you only need to specify one class.

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 do not specify a SqlMetaWhereClause in the parameters object. If you specify only an OrderGroup class without a SqlMetaWhereClause, the search filters results to that 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 – Combined with OrderSearchOptions.Classes to form a subquery based on an OrderGroup metaclass. For example, "META.CardType = 'Visa'" paired with "CreditCardPayment" in the Classes collection returns all orders, carts, and payment plans paid by Visa. META is the SQL alias for the table associated with the class you specify. See Classes.

SqlWhereClause – A SQL WHERE clause that lets you specify additional order search criteria to form a subquery in the search stored procedure.

The META keyword here refers to a different alias than the one used in SqlMetaWhereClause, which aliases 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 OrderGroup table is referenced directly in the SQL WHERE clause. For example, you could write OrderGroupId IN (SELECT OrderGroupId FROM Shipment WHERE ShipmentTrackingNumber IS NOT NULL).

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

The following fields are not used for order search:

JoinSourceTable

JoinSourceTableKey

JoinTargetQuery

JoinTargetTableKey

JoinType

OrderByClause – A SQL ORDER BY clause used to sort results. Use any column from the OrderGroup table, or META.<column-name> for columns in the metaclass table (for example, in OrderGroup_PurchaseOrder).

Mediachase.Commerce.Orders.OrderContext

This class is a singleton with several methods to find orders.

FindActiveOrders() – Retrieves purchase orders where the OrderGroup.Status is either InProgress or PartiallyShipped.

Current.Search<Cart>() – Finds shopping carts based on parameters and options specified with the two other classes.

FindPaymentPlans() – Finds payment plans based on parameters and options specified with the two other classes.

FindPurchaseOrders() – Finds purchase orders based on parameters and options specified with the two other classes.

FindPurchaseOrdersByStatus() – Retrieves orders with particular order statuses.

Order search code examples

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

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

The following examples have code that follows the code above.

Search purchase orders by tracking number pattern and shipment assignment

Search for purchase orders with a tracking number that is like a particular pattern, and with a tracking number (a metafield) assigned to at least one shipment 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.Search<PurchaseOrder>(parameters, searchOptions);

Search purchase orders by line item RMA number

Search for purchase orders containing line items with an RMANumber metafield assigned.

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.Search<PurchaseOrder>(parameters, searchOptions);

📘 Note Use the SqlWhereClause to ensure that only purchase orders are returned. Otherwise, the dataset may also include carts (or payment plans, if your system has any), and you see errors like Index 0 is either negative or above rows count.

This happens because the metadata object loader cannot reconcile the different metafields between purchase orders and carts. If you query orders using the API and do not specify which OrderGroup object is the metaclass, add this WHERE filter to the SqlWhereClause. You can make a compound WHERE clause that includes this filter.

Search shopping carts by line item expiration date

Search for shopping carts where line items have an expiration date (a metafield) 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.Search<Cart>(parameters, searchOptions);

Search purchase orders by shipment shipping status

Search for purchase orders that have shipments with a specific shipping status (as defined by a metafield).

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.Search<PurchaseOrder>(parameters, searchOptions);

Search orders by SKU name substring using a catalog join

This more complex SQL WHERE clause returns all orders that contain SKUs whose name contains a substring, by joining with the CatalogEntry table.

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.Search<PurchaseOrder>(parameters, searchOptions);

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

You can add any subquery, allowing the most flexibility.

Search orders across multiple metadata tables using joined SQL clauses

This pattern joins several order metadata tables in the SQL WHERE clause. It is the preferred approach for complex order searches with multiple order metaclasses, rather than relying on the options Classes collection.

📘 Note The following query may appear to return duplicate OrderGroupId values because 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.Search<PurchaseOrder>(parameters, searchOptions);

📘 Note Always test queries in SQL Server Management Studio first, and remember the closing ) for your SqlWhereClause property value.

Stored procedure breakdown

The first part of the order search runs a dynamic query based on the OrderSearchParameters and OrderSearchOptions properties set in code.

The following is the final query built from the settings you specify. The stored procedure ecf_OrderSearch uses these properties to produce a list of order IDs.

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]
    (<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 metadata query is built from the class you specify and the SqlMetaWhereClause (described above). The META where clause inside SqlMetaWhereClause is different from the META alias available to the SqlWhereClause.

If no SqlWhereClause is specified, the query runs without the AND [SqlWhereClause] clause. The same applies to SqlMetaWhereClause.

Commerce Connect manages the application ID internally.